Cell Value within a Formula

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
Is it possible for a Formula to pickup a key value based on a Value within a separate cell.

For example.

=VLOOKUP(A1,MONDAY!$A$1:$AZ$20,2,0)

Relates only To Monday, if the Cell, a cell , could be inserted so that MONDAY becomes an easily ( I hope) changed variable, that would help a great deal.

Thing is, I can;t get the syntax that would allow the cell value to display within a vlookup like that.

Has this been done before, or is it a screwy idea ? :)


Ta

(y)
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Have a lookup at INDIRECT, as in --

=VLOOKUP(A1,INDIRECT("'"&B1&"'!$A$1:$AZ$20",2,0)

where B1 houses your sheetname.
 

Guitarde

Board Regular
Joined
Mar 18, 2003
Messages
238
If your different ranges are not the same sizes on each page (a1:az20),
you can define some named ranges "Monday", "Tuesday" for each page.

Then type the range name in B1 and use =VLOOKUP(A1,INDIRECT(B1),2,0)
 

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
Ta

I'll give that a Go...

Sounds like the way !!!

(y) :pray: :pray: (y)
 

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
The datasets are exactly alike, so Just Jon's Formula Does the tricK...

=VLOOKUP(A1,INDIRECT("'"&A22&"'!$A$1:$AZ$20"),2,0)

Is the Key Format... I think All I added was the extra bracket :)

Now I can Move between 5 Pages of data Quite Easily, No Worries :)
All on one page.

Ta muchly.

(y)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,304
Messages
5,600,862
Members
414,409
Latest member
FloordAlex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top