Dynamic cell reference to workbooks using IF statement

[Gerry Poppe]

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.  

[#2 · Accepted solution]

@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))

[#3]

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?

[#4]

@Gerry Poppe 

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.

[#5]

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.

[#6]

@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.

[#7]

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.

[#8]

@Gerry Poppe Tried, but could not replicate the problem. Have a look at the attached sheet and see if it resembles your formulae.

[#9]

@Riny_van_Eekelen 

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????

[#10]

@Gerry Poppe Sorry, need to refresh my memory, Will get back later.

[#11]

Thanks,

Hopefully I have explained the current issues with the dynamic linking that I am trying to accomplish

[#12]

@Gerry Poppe Went through your follow-up question several times but am confused. You start by explaining that you want to "grab all documents" from one folder and move them.

Towards the end you mention that files may be in the E drive on one computer and perhaps in F on another computer. That suggests you are talking about links.

I trust you are able to concatenate the entire path (drive, folders, filename), similar to concatenating cell references withing a workbook. One of the cells referenced should hold the drive letter, where the user should enter the correct letter assigned to 'thumb' drive. You ask for "creative ideas". Not sure what I can do for you.

[#13]

Sorry for my poor explanation. Let me try this again. In my example of the directory tree that exists on a thumb drive I want to be able to retrieve data from the workbooks stacked under the root "proposed". I have concatenated the path to those specific files. I want to be able to create a master file in the root directory ("proposed") where I can retrieve various elements from the various files under the root. I want my formula to be able to be drug and the cell reference update as you drag the cell down or across. I cannot seem to be able to write the formula that will use my concatenated path and worksheet reference to enable me to retrieve the data from one of those stacked files below the root. The reason I want it dynamic is to eliminate the need to remap file locations when the thumb drive is moved from computer to computer.

[#14]

@Gerry Poppe Sorry for being slow in understanding. INDIRECT doesn't work with references to closed workbooks, so you can't do what I believe you have in mind. Can't think of any way how to achieve what you need.

[#15]

To your knowledge is it possible to create a formula for an open workbook?

[#16]

@Gerry Poppe Not sure if this is helpful. but let's say Sheet2 has data like in the picture below:

Then you could us a formula, any where else in the workbook to dynamically pick-up these numbers with this formula:

=INDIRECT("Sheet2!"&ADDRESS(ROW(A1),COLUMN(A1)))

The ADDRESS function basically returns the representation of "A1" to INDIRECT. Drag the formula down and it will become A2. Drag to the right and it will become B1 and so on.

In another sheet I entered the formula in D6 (dragged down and across) and it looks like this:

Just put the concatenated file and sheet name of the open workbook in stead of Sheet2! and it should work.  

[#17]

Thanks!!

Here is what I have for my concatenated formula
=(CONCATENATE('READ ME FIRST (INDEX)'!$A$7,"\",'Employee Contracts'!$F$4,'Employee Contracts'!F6,"\Current!"))

This is what the concatenation generates

C:\Users\gerryp\Documents\proposed\Employee Contracts\_Contract Details.xlsx\Current!

I created a Named value "DATA" for the cell where the above value was placed.

I then created, following your example, the following formula.

=INDIRECT("DATA" &ADDRESS(ROW(A2),COLUMN(C3)))

With the file "_Contract Details" open in Excel I was expecting results from cell C2 in the "Current" worksheet.

Is the placement of the ! mark in the formula an issue? Or is an issue with the file path being concatenated together with the file name?

[#18]

@Gerry Poppe If DATA is a named range you should not use the quote marks around it. With them you just create a path like "DATAC2". So it should work if remover the quote marks. And yes the ! has to be in the right place is important.

[#19]

@Riny_van_Eekelen 

I noted that when I am in edit mode that it appears to be referencing cells in my worksheet even though the reference to the "DATA" is to an open external workbook.

C:\Users\gerryp\Documents\proposed\Employee Contracts\_Contract Details.xlsx\Current! is named "DATA"

[#20]

@Gerry Poppe But if the workbook is open you would NOT include the entire file path. And now I understand that "DATA" is a named range in the targeted workbook. Perhaps I've lost you completely, but again, INDIRECT works only on open workbooks. Create a link to the "DATA" in the other (open) workbook and replicate what you see in that link in text which you can use with an INDIRECT function.