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

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

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

Which version of Excel are you using? If Excel 2003 or prior, you are
limited to 7 levels of nesting, in Excel 2007, you have 64 levels of
nesting.

Tyro


"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

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

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

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

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

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

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

Hi,

I have a similar issue, i have already posted this question but the person
answering my question hadnt done this before so couldnt provide much
guidance, and since you have can you please provide some guidance?

my previous email:

Hi, I have the following 2 formulae and i have been desperately trying
to
make them work but the dont!

I am looking up the value of column D in 1 worksheet to look across 10/20
different sheets (which are all named) and then return the results as "1,0"
when done into another sheet. So i am just looking up names from column D of
sheet"names" and then looking across 10 sheets, again in the same column - D (dont want it to return anything other than just the name, which will tell me if that is present anywhere witin the 20worksheets.)

These are the formulae i have tried. the 1st works on the sheets being
called sheets 1,2 etc and not being renamed and the 2nd formula allows you to
name the sheets then refer to them seperatly. i would like to use the 2nd as
i all my sheets are named.

=VLOOKUP(B1,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("sheet"&ROW(INDIRECT("1:10"))&"!d2:d100"),B 1)0,0)&"!d2:d100"),1,0)



=VLOOKUP(B2,INDIRECT(""&INDEX($A$1:$A$3,MATCH(TRUE ,COUNTIF(INDIRECT(""&$A$1:$A$3&"!d2:d100"),B1)0,0 ))&"!d2:d100"),1,0)


I either get the response N/A or VALUE. i dont know if i have got all the
referenes correct, maybe thats why its not working.

maybe the "d2:d100" is incorrect as i repeat it in the formula, but this is
the range where the lookup should look in.

I hope im making sense.

Thanks for your help.


please help!!

"T. Valko" wrote:

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

See my reply to your original post

--
Biff
Microsoft Excel MVP


"Gemz" wrote in message
...
Hi,

I have a similar issue, i have already posted this question but the person
answering my question hadnt done this before so couldnt provide much
guidance, and since you have can you please provide some guidance?

my previous email:

Hi, I have the following 2 formulae and i have been desperately trying
to
make them work but the dont!

I am looking up the value of column D in 1 worksheet to look across
10/20
different sheets (which are all named) and then return the results as
"1,0"
when done into another sheet. So i am just looking up names from
column D of
sheet"names" and then looking across 10 sheets, again in the same
column - D (dont want it to return anything other than just the name,
which will tell me if that is present anywhere witin the
20worksheets.)

These are the formulae i have tried. the 1st works on the sheets
being
called sheets 1,2 etc and not being renamed and the 2nd formula
allows you to
name the sheets then refer to them seperatly. i would like to use the
2nd as
i all my sheets are named.

=VLOOKUP(B1,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("sheet"&ROW(INDIRECT("1:10"))&"!d2:d100"),B 1)0,0)&"!d2:d100"),1,0)



=VLOOKUP(B2,INDIRECT(""&INDEX($A$1:$A$3,MATCH(TRUE ,COUNTIF(INDIRECT(""&$A$1:$A$3&"!d2:d100"),B1)0,0 ))&"!d2:d100"),1,0)


I either get the response N/A or VALUE. i dont know if i have got all
the
referenes correct, maybe thats why its not working.

maybe the "d2:d100" is incorrect as i repeat it in the formula, but
this is
the range where the lookup should look in.

I hope im making sense.

Thanks for your help.


please help!!

"T. Valko" wrote:

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

I have huge data in 16 worksheets. Now I want to create 17 sheet with vlookup

On Sunday, January 13, 2008 1:53 PM keithobr wrote:

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


On Sunday, January 13, 2008 2:57 PM T. Valko wrote:

Do your 7 sheet names follow some sort of sequential pattern like Week1,
Week2, Week3?

--
Biff
Microsoft Excel MVP


On Sunday, January 13, 2008 7:33 PM keithobr wrote:

Each sheet represents a different interview location with up to 200 names on
each. Why?

"T. Valko" wrote:


On Sunday, January 13, 2008 8:34 PM Tyro wrote:

Which version of Excel are you using? If Excel 2003 or prior, you are
limited to 7 levels of nesting, in Excel 2007, you have 64 levels of
nesting.

Tyro


On Sunday, January 13, 2008 10:27 PM T. Valko wrote:

I guess that means the answer to my question is no?


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
...


On Sunday, January 13, 2008 10:38 PM 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
...


On Monday, January 14, 2008 1:53 AM keithobr wrote:

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:


On Monday, January 14, 2008 12:54 PM 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
...


On Tuesday, January 15, 2008 1:17 PM keithobr wrote:

Hi biff

Afraid I do not 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:


On Wednesday, January 16, 2008 1:23 AM T. Valko wrote:

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
...


On Thursday, January 17, 2008 9:42 AM Gem wrote:

Hi,

I have a similar issue, i have already posted this question but the person
answering my question hadnt done this before so couldnt provide much
guidance, and since you have can you please provide some guidance?

my previous email:

Hi, I have the following 2 formulae and i have been desperately trying
to


please help!!

"T. Valko" wrote:


On Thursday, January 17, 2008 2:15 PM T. Valko wrote:

See my reply to your original post

--
Biff
Microsoft Excel MVP