Forum Discussion
Solved
Cell Formula Not Calculating
I've created a cell formula using the concatenate function. =CONCATENATE("='[",PriorFile," P & L Excel(39850).xlsx]Budget vs. Actuals'!$A8") When the variable "PriorFile" is set to "Oct 22" a...
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.
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!