Excel Excel Vlookup formula based on cell data

[jdavidc85`]

Hello, I am working on a spreadsheet to help employees quickly calculate tank volumes. In short, we keep track of tank volume by height in 16ths of an inch. I have 10 different worksheets containing the height and volume data of the 10 tanks. The sheets are named by tank number. So for example, tank 2301 is in worksheet "2301". In another reference sheet I plan to hide, In B column have the heights in 16ths of an inch. In C column I have the volume associated with that height. I do have a Vlookup formula that works right now but I would like to use one master sheet and need to be able to enter different tank numbers in a selected cell and have the formula reference the data sheet based on the tank number we enter. Each tanks volumes are different which is why this is needed. This is the formula I currently use and it works! Just not sure how to alter it so I can have a cells value determine the reference worksheet in the vlookup formula.

=VLOOKUP($B$3,'2301'!$B$2:$C$519,2)

Id like the 2301 value to change based on what tank number we type in which will also be the data sheets name to be referenced. Maybe adding a drop down selection would even be a better idea than typing the number in.

Can this be done?

Thanks in advance for any info!

Joe

 

[Becky]

Welcome to the forum

Hmmm that's a tricky one. So you want the table array to be dependent on the data you type in? I think you can use the INDIRECT function for that (with the function pointing at the data input cell), but you would need to give defined names to all the relevant ranges. For example, you could name the range '2301'!$B$2:$C$519 to something like Tank_2301.

You could use Data Validation to create a drop down list to select the right tank.

How does that sound?

 

[jdavidc85`]

Thank you! I have not used an Indirect function before. Could you give me am example? Luckily the data ranges will be the same cell and column wise on each sheet.

 

[Becky]

It doesn't matter whether the data ranges are the same, unfortunately you will have to name each one... That is, select the range, and assign a name to it by typing it in the name box. So it might take a while to set up, but I think it could work.

For example, if you gave the range B2:C519 on worksheet 2301 the name Tank_2301, and had Tank_2301 as text in (say) cell A1, then your formula could be:

=VLOOKUP($B$3,INDIRECT(A1),2)

Then the formula would be referring to the range B2:C519 on worksheet 2301. Likewise, when the named range in A1 changes, the range referred to in the formula would change.

I think. In theory. Give it a try and let me know if it works!

 

[RMG1999]

Why not create a single Master list of all the tanks in one tab.
You will need to create a single field with a code. I can think of something like this to enter in column A one below the other:
23010001 stands for tank 2301, 1/16 of an inch
23010002 stands for tank 2301, 2/16 of an inch
23010003 stands for tank 2301, 3/16 of an inch .. and so on for the entire height of 2301
Then follow by 23020001, 23020002, 23020003, etc for the next tank, and so on.
In column B, corresponding to each tank and 1/16th reference, you will put the volume.

In your query spreadsheet, you will put the tank # in one cell, the 16th inch number in another cell, and you can create a vlookup formula that combines the contents of these two cells and then does a look up in the master.

eg
if you entered 2301 in one cell and 87 in another cell (representing 87/16 of an inch), yo could write a formula like 2301*10000+87 to create the code (23010087 which would stand for tank=2301, height=87/16 inch), then do a vlookup for this code in the master list

Hope this helps

 

[Sampath]

Hi,

Can someone please help me.

I want to get the value of the Single Room Rate or the Double Room Rate from the drop down lists I have created on the 1st Sheet. The drop down Lists have to give me the Result after i select from the 1st sheet.

I can email the Table as I can't attach the table here. Please email the email address of the person who can help.

thanks,
Samapth.

 

[myturner]

This worked for me. Signed up just to say thanks!

IF($D40="GL",VLOOKUP($B40,INDIRECT(AD$3),7,FALSE)...

AD$3 contains the named range in the work book.

 

[Becky]

This worked for me. Signed up just to say thanks!

You're welcome! Thanks for letting us know