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