Cell Formula Not Calculating

[LaPaz5640]

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" and I hit the enter key, the following is displayed. The displayed formula is not calculated.

='[Oct 22 P & L Excel(39850).xlsx]Budget vs. Actuals'!$A8

Yet if I were to type in the above string of characters, it works! Does anyone have ideas as to what is happening here? I've made sure that cell formatting has been set to General.

Thanks

[#2]

Hi @LaPaz5640 

It seems like there is a space in before or after the variable, check that or use trim function

=CONCATENATE("='[",Trim(PriorFile)," P & L Excel(39850).xlsx]Budget vs. Actuals'!$A8")

[#3 · Accepted solution]

@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.

[#4]

@Riny_van_Eekelen 

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.

[#5]

@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.

[#6]

Greatly appreciated!