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))
Gerry Poppe Tried, but could not replicate the problem. Have a look at the attached sheet and see if it resembles your formulae.
Riny, You helped me back in 2020 with a linking issue. I have a new one that has me puzzled. I have created a link (concatenated path etc) for the various locations of workbooks stacked underneath what I would refer to as a root directory.
Proposed (root Directory)
Employee contracts (sub directory)
_Contract Details.xlsx (Workbook)
Current (Sheet1)
Business contracts
_Maintenance Details.xlsx
Office Leases
_Collier Leases.xlsx
In the example above I have concatenated the following C:\users\gerryp\documents\proposed\
What I want to do is to make a link that incorporates the concatenated link above and adds the subdirectory of Employee Contracts and adds to that the file name _Contract Details.xlsx and the sheet reference Current and the cell which I want to retrieve the value from c2.
If I could create a dynamic link it would help accomplish the ability to grab all documents in the folder "Proposed" and move it in its entirety to a thumb drive or from a thumb drive to a different hard drive location and only need to update one path in the master spreadsheet and have that change cascade for all linked workbooks inside my master spreadsheet.
I had previously just link the normal method but soon discovered that when copying everything to a thumb drive that my data links were no longer valid due to the absolute values which were created to a prior location.
Additionally if the thumb drive registered as drive E on one PC and as drive F on a different PC there was a lot of work to keep changing the locations of the linked workbooks.
Would you have any creative ideas on how to accomplish my madness????
Gerry Poppe Sorry, need to refresh my memory, Will get back later.
- Thanks,
Hopefully I have explained the current issues with the dynamic linking that I am trying to accomplish