CODEWITHSUNDEEP
excelexcel-formula
Heinrich
Heinrich

Reputation: 11

using filename in vlookup

I am trying to find a non-vba solution for getting a value via vlookup. The vlookup should use the sheet name as the search criteria. The sheet name format is "00000"

=VLOOKUP(N1;[otherfilename.xlsx]othersheetname!$A$3:$C$10000;3;false)

when writing "12345" into Cell N1 it work perfectly fine when writing in Cell N1:

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]", CELL
("filename",A1))-FIND("[",CELL("filename",A1))-6)

the Cell returns also "12345" but VLOOKUP does not work anymore.

Why is this different to straight putting a value into the Cell? How do I solve this?

Solution: Wrapping VALUE() around MID()

Upvotes: 1

Views: 575

Answers (1)

zipa
zipa

Reputation: 27869

If you want to reference the location that is generated from string you should use INDIRECT() like this:

=INDIRECT("[otherfilename.xlsx]othersheetname!$A$3:$C$10000")

This will return values from the range in desired sheets.

Upvotes: 2

Related Questions