Here's a small sample that demonstrates this:
vlookup across multiple sheets.xls 20kb
http://cjoint.com/?bqhmgKwUIV
Select an employee name from the drop down in cell A2 on Sheet1.
Lookup the employee's name and return that employee's manager. The
employee's name could be on any of 6 sheets (but will only appear on 1).
Sheet1 B2 returns the employee's manager
Sheet1 C2 returns the sheet name where that employee's info can be found
--
Biff
Microsoft Excel MVP
"keithobro" wrote in message
...
Hi biff
Afraid I don't really understand.
Let's say my 7 sheets are named on their tabs:
Alt
Bel
Met
Pan
Pet
Swi
How do I start?
Sorry to be so "dim"!
keith
"T. Valko" wrote:
You can put things wherever you want them. The ranges/references I used
are
just for demonstration purposes.
Suppose your lookup_value was in cell A1. Typically, you'd want the
result
of the lookup formula next to the lookup_value so you would probably
enter
the lookup formula in cell B1. You can put the list of sheet names
anywhere.
If you're using this on some sort of form or report then you'd probably
want
the list of sheet names outside of the report or form.
--
Biff
Microsoft Excel MVP
"keithobro" wrote in message
...
Many thanks Biff:
I think I understand. Will try it out when I get to work today.
If i understand correctly, my first task is to list the sheet names,
but
where should I put this?
Keith
"T. Valko" wrote:
Correction: I used the wrong range in the COUNTIF function. Should be:
=VLOOKUP(A1,INDIRECT("'"&INDEX(WSList,MATCH(TRUE,C OUNTIF(INDIRECT("'"&WSList&"'!A:A"),A1)0,0))&"'!A :B"),2,0)
--
Biff
Microsoft Excel MVP
"T. Valko" wrote in message
...
Each sheet represents a different interview location
I guess that means the answer to my question is no?
Why?
The information you provide will determine what kind of suggestions
you'll
get.
Make a list of your sheet names and give this list a defined name:
B1:B7 = list of sheet names = defined name WSList
A1 = lookup value
Try this array formula** :
=VLOOKUP(A1,INDIRECT("'"&INDEX(WSList,MATCH(TRUE,C OUNTIF(INDIRECT("'"&WSList&"'!B1:B7"),A1)0,0))&"' !A:B"),2,0)
Assumes the table_array on each sheet is in the range A:B.
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
--
Biff
Microsoft Excel MVP
"keithobro" wrote in message
...
Each sheet represents a different interview location with up to 200
names
on
each. Why?
"T. Valko" wrote:
Do your 7 sheet names follow some sort of sequential pattern like
Week1,
Week2, Week3?
--
Biff
Microsoft Excel MVP
"keithobro" wrote in message
...
I want to get a master worksheet to check for a candidate's
details
across
7
other worksheets. If the VLOOKUP fails to find that person on
sheet
1,
it
tries sheet 2, then sheet 3 and so on.
can this be done? is it a question of replacing the FALSE part
with
the
next
VLOOKUP formula?
Thanks.
Keith