Forum Discussion

LaPaz5640's avatar
LaPaz5640
Copper Contributor
Nov 21, 2022
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...
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Nov 21, 2022

    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.

Jihad Al-Jarady's avatar
Jihad Al-Jarady
MCT
Nov 21, 2022

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")

 

Resources