Forum Discussion
Cell Formula Not Calculating
LaPaz5640 CONCATANATE creates a text string that is not recognised as a formula, no matter how your format the cell it is in.
What you probably need is this:
=INDIRECT(CONCATENATE("'[",PriorFile," P & L Excel(39850).xlsx]Budget vs. Actuals'!$A8"))
And the referenced xlsx file must be open.
Thanks for your help. Interestingly, when I try to copy and past this revised code to other rows below, the cell reference $A8 did not paste as $A9, $A10, etc., but remained as $A8 for all copied cells and I had to manually edit each row.
LaPaz5640 That's because the $A8 is part of the quotation marks. You need to make the 8 dynamic using the ROW function.
Change the formula to:
=INDIRECT(CONCATENATE("'[",PriorFile," P & L Excel(39850).xlsx]Budget vs. Actuals'!$A",ROW(A8))
The ROW(A8) bit will concatenate the number 8. Drag it down and it will become 9, 10 etc.
- Greatly appreciated!