Trying to use the contents of a cell as an argument in a vlookup

[fgunning]

I'm trying to create a vlookup that references different documents depending on other items in the row. The document name can be created by combining these other items in the row, so I have made a column which contains the document name, as well as the array within that document that I'd like to look at. I need to use the contents of this cell as the second argument in my vlookup.

The function is currently turning up an error, and I believe that is something to do with the formatting of the second argument. My vlookup has the form =vlookup(K4, "'Location&C4&F4&Name'!$G:$T", 14,0), where C4 and F4 are the parts of the document name being pulled from my spreadsheet. Does anyone know why this is returning an error?


Thanks!

[fgunning]

The function in the original post is simplified to avoid complexity, but for the sake of completeness here's the entire function.

=VLOOKUP(K4,"'P:\PROJECT CONTROLS\Invoicing\Category Assignments\"&C4&" - "&F4&"\["&C4&" "&1*RIGHT(L4,3)&" Lines.xslx]Sheet1'!$G:$T",14,0)

[dredwolf]

It returns an error, because it is simply a line of text, you need to wrap it with INDIRECT function to get a valid reference ie:
=vlookup(K4, INDIRECT("'Location"&C4&F4&"Name'!$G:$T", 14,0)
or something like that

Hope this helps

[FDibbins]

excel formulas cannot, by themselves, use text as a reference in a formula. You need to use INDIRECT() wthin the formula, to convert the text to a reference that the formula can use/understand.

However, INDIRECT() by itself, does not work with closed workbooks, it will return an error. You need to install the MoreFunc add-in for that

[dredwolf]

@ FDibbins, good point, I wrote my post before the second post appeared and assumed it was within same workbook...

[fgunning]

This forum is amazing, thank you guys. Theoretically, if I open the workbook to which it is referring, and plug in INDIRECT before that string, it would work?

[hectop]

What do you mean by different documents? Different sheets?
I would suggest the main problem is that

Please Login or Register  to view this content.

is a text string. Not a range.
I've never seen anyone put in a variable for the search range.

[dredwolf]

Theoretically, yes, but you may still have spelling issues, which would cause errors, (ie- missing spaces, extra spaces, non-printing caracters, etc...)
As long as you are aware of that, it should work

[FDibbins]

Thanks for the kind words, always appreciated

yup, if you have constructed the INDIRECT() properly - they can be a bit faniticky sometimes.

=vlookup(K4, "'Location&C4&F4&Name'!$G:$T", 14,0)
=VLOOKUP(K4,"'P:\PROJECT CONTROLS\Invoicing\Category Assignments\"&C4&" - "&F4&"\["&C4&" "&1*RIGHT(L4,3)&" Lines.xslx]Sheet1'!$G:$T",14,0)

maybe something like this (untested)...
=vlookup(K4,indirect("'"&Location&"'"&C4&F4&Name'!$G:$T", 14,0)

[fgunning]

I know that the document its referring to obviously isn't available to you guys, but if you could at least help me match up the equations it'd be amazing. I attached a sample workbook. I'd like cell U4 to give the same readout as S4. If I have to open the workbook to which it is referring that's fine, but what I'm trying to do is have a variable as my second argument in a vlookup.

Also I've just started using excel and I'm trying to guess-work my way through this, so any pointers would be amazing, as would saying "hey sorry, that's impossible"

Thanks y'all

[FDibbins]

If you already have the path and range ref in a cell (like you have in Y6?), then the INDIRECT() becomes pretty simple (I hope lol)

=VLOOKUP(K4,INDIRECT(Y6),14,0)

Pointers...
you dont always need to use CONCATENATE(), you can use & to combine things. B4 can shortened from...
=CONCATENATE(F4,G4,"00",J4)
to...
=F4&G4&"00"&J4
F4=1*(LEFT(E4,2))
F4=1*LEFT(E4,2)
nothing real earth-shattering, just a bit simpler

[dredwolf]

Well, I get a #REF error, because I don't have that file, but I think if the workbook existed, but was closed, you would receive this error as well
what occurs when it runs for you?

[dredwolf]

Also noticed if you type this equation into an empty celll
=Y6=Y4
it returns false, so the 2 strings are not EXACTLY the same...

found it...the one name is...xlsx and the other is ....xslx

this, of course, is not the same, spelling must be exactly the same
other than that the formula looks fine

Hope this helps

[fgunning]

It works!! (You're right, the workbook has to be open. When its closed, it gives a #REF error)
Holy jesus. I just got a new job (why I'm learning excel) and they spend hours a day referencing all the different files manually. I figured it had to be possible in excel, and then quickly figured out that I didn't know enough excel. Enter, you guys. You've just saved my entire office a ton of work. Daily.

My only regret is that I can only add rep once per post. Yall are the best.

[FDibbins]

Thanks for the kind words andthe feedback

[fgunning]

One last pair of questions. (Maybe I shouldn't say last. This is a really fun rabbithole I'm going down).

One is an excel question. The equation I've written does vlookups on documents, and like we stated above, it only works on open workbooks. Is there any way to work around that? The function will reference about 40 different workbooks, and it'd be nice to not have to open them all. Even if this means purchasing an add-on or somesuch, this would be worth it to me.

If not, I have a simple windows question. I want to open about 40 excel files at once. I tried highlighting, right-clicking, and selecting "Open", but this only opened one document. I think this might be because upon opening the document there is a prompt (I need to enable content for most of these docs) and therefore interrupts the opening of the rest. Do you know any way around that? Can I somehow set it to automatically enable content, thus avoiding the prompt?

[FDibbins]

1. There is an add-in called Morefunc that you can install that will let you use INDIRECT on closed workbooks (cant recall teh site, but google it)

2. you can use "save workspace (view tab)

[Tony Valko]

The Morefunc add-in can be downloaded from here:

http://excelenthusiasts.sharepoint.c...s/Morefunc.zip

However, it has not been updated to work with the recent versions of Excel. It may or may not work in Excel 2010. I have only used it in Excel versions 2007 and earlier.

If you're able to install the add-in and it works the function you want to use is called INDIRECT.EXT.

It works much like the built-in function INDIRECT except that it can be used to reference closed files.