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