Excel cell reference using a string

0
votes

In an Excel worksheet I have a VLookup. On the VLookup it references data on another sheet. For example,

VLOOKUP($B$3,'Inventory Sep'!$A$2:$C$255,3,FALSE)

Monthly I need to change the Inventory sheet to update it for example to Inventory Oct or Inventory Nov.

Rather than asking the user to update the VLOOKUP by editting the inventory sheet I would like to make the VLOOKUP reference a certain cell for example:

VLOOKUP($B$3,'Maintenance!A1'!$A$2:$C$255,3,FALSE)

Then the user would just have to go to the maintenance tab and update the A1 cell changing it from Inventory Sep to Inventory Oct

Is it possible to reference a cell that refers to a sheet in excel and how would I do it?

1
excelexcel-formula
You need to build a proper reference to a sheet. Check the INDIRECT spreadsheet function. – FDavidov
Thank you, using the INDIRECT spreadsheet function works and returns exactly what I was looking for. Here is my updated VLookup: VLOOKUP($B$3,INDIRECT("'"&D5&"'!"&"$A$2:$C$300"),3,FALSE) – Jean-Claude
You are welcome. By the way, you may wish to up-vote my comment if it was helpful for you. – FDavidov
I think you need 15 reputation to upvote comments. So I can't upvote comments, I can only select which Answer has helped. – Jean-Claude

1 Answers

0
votes

As FDavidov said, just use INDIRECT():

=VLOOKUP($B$3,INDIRECT("'"&Maintenance!A1&"'!$A$2:$C$255"),3,FALSE)