Use tab name in Vlook Up
 

I have a formula that looks up data in a sheet using Vlookup, every week the
formula changes as the week number changes.

The tab to look for data has a tab name of wk## and thus the forumla for
week 28 looks like this

=VLOOKUP(O2,wk28!A4:L3000,12,FALSE)

How can I substitue the wk28 tab number in the formual for a cell reference
that contains the text "wk28" ?

This will save me modifying forumlas on weekly basis.

Thanks

Use tab name in Vlook Up
 

Have a look in the help index for
INDIRECT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"AMH" wrote in message
...
I have a formula that looks up data in a sheet using Vlookup, every week
the
formula changes as the week number changes.

The tab to look for data has a tab name of wk## and thus the forumla for
week 28 looks like this

=VLOOKUP(O2,wk28!A4:L3000,12,FALSE)

How can I substitue the wk28 tab number in the formual for a cell
reference
that contains the text "wk28" ?

This will save me modifying forumlas on weekly basis.

Thanks

Use tab name in Vlook Up
 

With something like
=VLOOKUP(O2,INDIRECT("wk"&O1&"!A4:L3000"),12,FALSE )
where O1 hold the week number
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"AMH" wrote in message
...
I have a formula that looks up data in a sheet using Vlookup, every week
the
formula changes as the week number changes.

The tab to look for data has a tab name of wk## and thus the forumla for
week 28 looks like this

=VLOOKUP(O2,wk28!A4:L3000,12,FALSE)

How can I substitue the wk28 tab number in the formual for a cell
reference
that contains the text "wk28" ?

This will save me modifying forumlas on weekly basis.

Thanks

Use tab name in Vlook Up
 

Hi Don,


I've used INDIRECT before to produce text strings, however I couldnt workout
from the help text how to use it within the formula I listed, I tried this


=VLOOKUP(O2,INDIRECT("O1")*A4:L3000,12,FALSE)

where O1 contains the tab name I want to refer to, but this didnt work


"Don Guillett" wrote:

Have a look in the help index for
INDIRECT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"AMH" wrote in message
...
I have a formula that looks up data in a sheet using Vlookup, every week
the
formula changes as the week number changes.

The tab to look for data has a tab name of wk## and thus the forumla for
week 28 looks like this

=VLOOKUP(O2,wk28!A4:L3000,12,FALSE)

How can I substitue the wk28 tab number in the formual for a cell
reference
that contains the text "wk28" ?

This will save me modifying forumlas on weekly basis.

Thanks

Use tab name in Vlook Up
 

Did you see Bernard's?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"AMH" wrote in message
...
Hi Don,


I've used INDIRECT before to produce text strings, however I couldnt
workout
from the help text how to use it within the formula I listed, I tried this


=VLOOKUP(O2,INDIRECT("O1")*A4:L3000,12,FALSE)

where O1 contains the tab name I want to refer to, but this didnt work


"Don Guillett" wrote:

Have a look in the help index for
INDIRECT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"AMH" wrote in message
...
I have a formula that looks up data in a sheet using Vlookup, every week
the
formula changes as the week number changes.

The tab to look for data has a tab name of wk## and thus the forumla
for
week 28 looks like this

=VLOOKUP(O2,wk28!A4:L3000,12,FALSE)

How can I substitue the wk28 tab number in the formual for a cell
reference
that contains the text "wk28" ?

This will save me modifying forumlas on weekly basis.

Thanks

Use tab name in Vlook Up
 

Yes thanks to you both, now fixed, just wasnt using INDIRECT quite right

"Don Guillett" wrote:

Did you see Bernard's?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"AMH" wrote in message
...
Hi Don,


I've used INDIRECT before to produce text strings, however I couldnt
workout
from the help text how to use it within the formula I listed, I tried this


=VLOOKUP(O2,INDIRECT("O1")*A4:L3000,12,FALSE)

where O1 contains the tab name I want to refer to, but this didnt work


"Don Guillett" wrote:

Have a look in the help index for
INDIRECT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"AMH" wrote in message
...
I have a formula that looks up data in a sheet using Vlookup, every week
the
formula changes as the week number changes.

The tab to look for data has a tab name of wk## and thus the forumla
for
week 28 looks like this

=VLOOKUP(O2,wk28!A4:L3000,12,FALSE)

How can I substitue the wk28 tab number in the formual for a cell
reference
that contains the text "wk28" ?

This will save me modifying forumlas on weekly basis.

Thanks