Forum Discussion
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 worksheets based upon a value.
=IF((VLOOKUP(1,(INDIRECT("'"&$E$1&"'!A28")),1,FALSE))=1,INDIRECT("'"&$E$1&"'!b28"),IFERROR(IF(VLOOKUP(100,INDIRECT("'"&$E$1&"'!A28"),1,FALSE)=100,INDIRECT("'"&$E$1&"'!b28"),0),))
In the equation $E$1 is a concatenated field that changes based upon criteria. The concatenated value is the worksheet tab title.
In the above equation I can separate the two if statements into two stand alone statements and they will both work independently. Unfortunately when I place them into a single statement it fails and reports a #N/A error.
Possibly there is a better formula that will allow the lookup access. The other frustration is that to copy the formula down in the cells I manually have to adjust the rows as there does not appear to be a functional way to increment the row number when using the INDIRECT command.
Any guidance would be appreciated.
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))
19 Replies
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))- 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?
- 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.
