Forum Discussion

Bill114's avatar
Bill114
Copper Contributor
Mar 07, 2019
Solved

Execute a concatenated text string?

I am referencing a specific named tab in a reference workbook, and have the following command.   =CONCATENATE("'[Reference file example.xlsx]",B8, "'!$B2")   In my present (separate) workbook, I ...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 07, 2019

    You need to wrap with INDIRECT

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 07, 2019

You need to wrap with INDIRECT

  • Bill114's avatar
    Bill114
    Copper Contributor
    Mar 07, 2019

    I was able to use the indirect function, but had to do it in 2 steps:

     

    In, for say, cell H25 I place the formula:  

    =CONCATENATE("'[Reference file example.xlsx]",B20, "'!$B2")

     

    It produces the string 

    '[Reference file example.xlsx]Monday'!$B2

     

    I can then use the statement

    =INDIRECT(H25)

     

    To produce the desired result

    Monday

     

    I can do this in 2 steps, but I'd rather do it all in one statement.  If I do, it gives me a #REF! error.  The other workbook is open.   

     

    I appreciate the help!!

     

     

     

     

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Mar 07, 2019

      Formula works, I reproduced it

      Could you attach sample file to check what's wrong?

      • JoeCavasin's avatar
        JoeCavasin
        Brass Contributor
        Oct 12, 2020

        SergeiBaklan hoping you can help with a similar issue.  Cell B14 is where i am trying to get a relatively complicated output.  When i wrap the existing concatenate formula with Indirect, it get a #REF error, and when I do a separate cell with =Indirect($B$14), i still get the #REF error.

         

        idea behind the formula for B14 is:

        (B10/B11), Rounded as per the values in B12 and B13, so in this case: ROUNDUP(B10/B11,0)

         

        I've even attempted a few reworkings of the concatenate formula to nest indirect within the formula to pull values in to the string, but all errored.  Any assistance would be appreciated

         

         

        Thanks

        joe

         

Resources