#REF! for some formulas

[Ankita_123]

I have a datatable with formulas, when I insert that datatable in excel, for some formulas it is showing #REF!. But for some formulas it is taking proper formula. so, Not able to find out the exact cause.

I am inserting the dt in excel like this.
sheet.InsertDataTable(dt, true, 1, 1,true)

dt contain formulas like this: =VLOOKUP(G4,'FR-BF-Qty-QS0'!$G:$H,2,0)*VLOOKUP(G4,'FR-Price-PS0'!$G:$H,2,0)

but in excel it appear like this: =VLOOKUP(G4,'FR-BF-Qty-QS0'!$G:$H,2,0)*VLOOKUP(#REF!,'FR-Price-PS0'!$G:$H,2,0)

Is there any possible cause for this?

[Marcia.Zhou]

Hello,

Thanks for your inquiry!

I simulated a database and did an initial test with the latest Spire.Xls v11.1.4, but did not reproduce your issue. The content of my data table and my output Excel formula are shown in the following screenshots.

database.png (43 KiB) Viewed 832 times

Excel.png (5.74 KiB) Viewed 832 times

To help us find out the reason of your issue, please provide us with your input file for investigation.

Thanks in advance.

Sincerely,
Marcia
E-iceblue support team

[Ankita_123]

hello,

Thank you for your reply.

I guess have figured out the issue. It is giving #REF! when it is referencing the same sheet.

so the formula that we were using is this =VLOOKUP(G2,'FR-BF-Qty-QS0'!$G:$H,2,0)*VLOOKUP('FR-BF-Value-QS0_PS0'!G2,'FR-Price-PS0'!$G:$H,2,0) in sheet FR-BF-Value-QS0_PS0,

then I have removed the sheet name. Like this =VLOOKUP(G2,'FR-BF-Qty-QS0'!$G:$H,2,0)*VLOOKUP(G2,'FR-Price-PS0'!$G:$H,2,0)

and now it is working. Confirm if self referencing is an issue.

Thank you.

[Marcia.Zhou]

Hello,

Thanks for sharing more information and sorry for the late reply as weekend.

I simulated a database with self-referencing in the formula but still not reproduce the issue. The content of my data table and my output Excel formula are shown in the following screenshots.

database.png (67.75 KiB) Viewed 817 times

self-reference.png (10.6 KiB) Viewed 817 times

Please make sure that you are using the latest Spire.Xls v11.1.4, and please provide us with your input file for further investigation. To protect your privacy, you can send it to us via email (support@e-iceblue.com).

Thanks in advance.

Sincerely,
Marcia
E-iceblue support team

[Marcia.Zhou]

Hello,

Hope you are doing well!

How is the issue now? If it still exists, could you please provide us with your input file for further investigation? You can send it to us via email (support@e-iceblue.com).

Thanks in advance.

Sincerely,
Marcia
E-iceblue support team