Forum Discussion
Dynamic cell reference to workbooks using IF statement
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))
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.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.
Try this:
=IF(OR(OFFSET(INDIRECT($E$1&"A28"),ROW(A1)-1,0)=1,OFFSET(INDIRECT($E$1&"A28"),ROW(A1)-1,0)=100),OFFSET(INDIRECT($E$1&"B28"),ROW(A1)-1,0),0)It becomes a bit less clear, but it works. We still look at the "fixed" cell A28, but offset it by 1 row down for every cell we copy down the formula. That's done by the "ROW(A1)-1" part in the formula.
But, be aware that extensive use of the OFFSET function may slow down responsiveness of Excel.
The attached workbook includes a working example. I trust it helps you in implementing it in your own sheet.
