Forum Discussion

Gerry Poppe's avatar
Gerry Poppe
Copper Contributor
Mar 27, 2020
Solved

Dynamic cell reference to workbooks using IF statement

I have a workbook which contains multiple years of data.  Each year is in a separate worksheet.  I want to be able to use a dynamic link which would allow me to gather data from anyone of the workshe...
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Mar 28, 2020

    Gerry Poppe 

    First of all, I would include the single quotes and the exclamation mark when you concatenate the sheet name you want to call with the INDIRECT function. For example =CONCAT("'"&B1&" "&B2&"'!") to produce something like 'Sheet Name'! (where B1 contains "Sheet" and B2 contains "Name"). Better to do that once than every time you use INDIRECT. Then, the formula itself seems overly complicated for what you want to do.

     

    Your first VLOOKUP could be replaced by:

    IF(INDIRECT($E$1&"A28")=1

     

    and your second one by:

    IF(INDIRECT($E$1&"A28")=100

     

    Similarly, the parts directly after each IF statement can be simplified to: INDIRECT($E$1&"B28")

     

    But then, the logic of the formula says: If a value equals 1 then return the value in 'Sheet Name'!B28. If it is not then see if it equals 100 and if so, then also also return the value from 'Sheet Name'!B28". Otherwise, return 0.

     

    The same logic would also be achieved by this:

    =IF(OR(INDIRECT(E1&"A28")=1,INDIRECT(E1&"A28")=100),INDIRECT(E1&"B28"),0)

     

    If you insist to work off your original formula (with some small modifications), this one will work as well and it will have eliminated the #NA! error.

    =IF(IFERROR(VLOOKUP(1,(INDIRECT("'"&$E$1&"'!A28")),1,FALSE),0)=1,INDIRECT("'"&$E$1&"'!b28"),IF(IFERROR(VLOOKUP(100,INDIRECT("'"&$E$1&"'!A28"),1,FALSE),0)=100,INDIRECT("'"&$E$1&"'!b28"),0))

     

     

Gerry Poppe's avatar
Gerry Poppe
Copper Contributor
Mar 30, 2020
Thanks, that did streamline the equation. Since the "" around the A28 and B28 lock that cell into the equation preventing the cell from incrementing as you drag the formula down in the spreadsheet do you have any suggestions on how to write it to allow the formulas to update the cell references as you drag it down?
Gerry Poppe's avatar
Gerry Poppe
Copper Contributor
Mar 30, 2020
I cannot say I have ever encountered this situation before. With the INDIRECT now successfully pulling data I have encountered one worksheet where the naming convention is not apparently working. There is only one tab that I cannot pull data from using the dynamic linking.

I have renamed that tab and then changed it back and I still cannot access the data, using the formulas. Is there another area where the name is stored that might be corrupt such that the INDIRECT function cannot locate it?
I have 7 tabs which work flawlessly using the INDIRECT function. Only this one tab seems to have issues.
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Mar 31, 2020

    Gerry Poppe Can't tell if you don't tell me the name of the sheet and show exactly the content of the cell that you call with INDIRECT. Can be a missing apostrophe (or one too many) or a space that should or should not be there. Or any other small typo. Can not imagine it to be specific to the name of one sheet alone. If Excel allowed you to (re)name it, you should be able call for it in any function.

    • Gerry Poppe's avatar
      Gerry Poppe
      Copper Contributor
      Mar 31, 2020
      I have a tab labeled DayPart Summary which contains the field (year) that I concatenate with an entity to create "'entity year'!" and "entity year-1'!" I then use those values on another tab to source the data for each entity and year to create comparisons. All of the other tabs are labeled Entity Year. The function works for 7 tabs to source the data as I change the year on my DayPart Summary tab. The tab that is not working I have tried renaming and then naming back in the Entity Year format. If I put the year as 2020 I get a comparison of 2020 to 2019 for each entity. If I place the year at 2019 I get a comparison of 2019 to 2018 for the entities with the exception of that one tab for 2018. I source nothing for that one entity. If I make the year 2018 I get all 2018 and 2017 data with the exception of the single entity and 2018 tab. My Naming conventions are Entity= WBBH & WZVN and my Year=2020, 2019, 2018, 2017. When I match up the tab with 'WBBH 2018'! no data will appear.

Resources