Reference another worksheet (in the same workbook) in Excel formula

[cu202]

I have the following formula which is referencing another worksheet (All Sales) however the code keep looking for All Sales on the C drive.

cells[“B7”].SetSharedFormula("=IF(ISNA(VLOOKUP($A7,All Sales!$A$8:$Q$39,6,FALSE))=TRUE,0,VLOOKUP($A7,All Sales!$A$8:$Q$39,6,FALSE))", 128, 1);

How do you get it simply to look for All Sales in the current workbook?

[Amjad_Sahi]

Hi,

Well, for your case (as you are referencing to different sheets in the formula(s)) Shared Formula feature won’t work. So, you should not use Shared formula feature here. For your information, in MS Excel, if a formula refers to other worksheet, it will be copied to each cells and it is not applied/ added to shared formula(s) list. For confirmation, you may unzip the file (you may perform all the steps in MS Excel manually and save the file) and check the source .xml file for the sheet, you will see there is no shared formula with its attribute(s) described there. So, MS Excel actually copies the cell’s formula from one cell to another cell but not set shared formulas.

For your situation, you should try to use Range.copy(sourceRange) accordingly instead, you got to write your own sample code code accordingly. You may see the sample code (so you could write your own code accordingly) in your other thread for your reference:

https://www.aspose.com/community/forums/permalink/834846/834855/showthread.aspx#834855

Thank you.