can I use the indirect function for this? vlookup with multiple workbooks.

[dooder]

Quick question version, I want to include a variable in a vlookup function to direct it to a specific worksheet.

Slightly longer version
Currently I have a pretty big dynamic spreadsheet. To simplify, let's say I have a list of 30,000 userid in a "master" workbook. Each user has a col indicating a certain status (Y/N). This status changes frequently (and by different people), so it has a separate workbook. I then do a simple vlookup by userid in the "master" workbook. I have a "current" sheet in that workbook that has the current Y/N status. Quarterly, I need to lock this status, so I create new sheet title 2019Q1, 2019Q2, etc, where I copy/paste the current sheet (and protect it).


My issue is that frequently, I need to recreate an output from my "master" workbook using a particular quarter's data. My current process is to do a find/replace, and change "current" with "2019Q1" or 2020Q1", etc. across the whole workbook After a lengthy recalculation, I do my outputs. It works fine, but seems clunky and slow. Is there a better way... maybe using the indirect function? Basically, I want to plug a 20XXQY variable in, and have all the vlookups be directed to a specific worksheet.

thanks in advance.

 

[jasonb75]

You can use indirect to recreate a range as a string in any place where you would use a normal range.

You would need to rewrite all of your formulas as INDIRECT, it can get complicated if you need any of the references to be relative.

INDIRECT is also a voliatile function, so the calculation overheads could likely increase.

 

[dooder]

So how would that work? I have a =vlookup(userid var,'\\network path\[statussheet.xlsx]Current'!$A:$B,2,FALSE). Let's say I make a reference cell where I can type in the either Current, XXXXQ1, XXXXQ2... etc. I want that value to replace the "Current" part of the vlookup. Can I do that?

 

[jasonb75]

You could do it with

=vlookup(userid var,INDIRECT("'\\network path\[statussheet.xlsx]"&cell&"'!$A:$B"),2,FALSE)

 

[dooder]

thanks, but doesn't seem to work. It's not seeing the cell value between the concatenates. Excel give me an error to select one of the valid sheets in the workbook.

s
You could do it with

=vlookup(userid var,INDIRECT("'\\network path\[statussheet.xlsx]"&cell&"'!$A:$B"),2,FALSE)

 

[dooder]

thanks, but doesn't seem to work. It's not seeing the cell value between the concatenates. Excel give me an error to select one of the valid sheets in the workbook.

never mind, it works!

 

[dooder]

meh, I swear it was working for a bit, but I think I have a problem with quote marks.

 

[jasonb75]

If you post what you have I can look for any errors in the formula.

edit:-

Have you closed the workbook that the formula is retrieving the data from? INDIRECT only works with workbooks that are open.

 

[dooder]

ahh, closed workbook was the issue. Well that would be a problem since I can't keep that workbook open all the time. I'll have to rethink it a bit.

 

[jasonb75]

In that case, your existing find and replace method is the only practical way. (Personally, I would use find and replace over INDIRECT anyway).