Sorry Biff for my lack of explanation.

I am trying to look up a number that could be on any one of the twelve
worksheets. I hope this clarifies what I'm looking for.

stm
Biff Wrote:
Hi!

It's not clear what you want.

Do you want to lookup a value that is on each sheet or do you want to
lookup
a value that may be on any sheet?

Biff

"sonic-the-mouse"
wrote
in message ...-

I have been tasked with streamlining a spreadsheet used at work. It
previously had 28 worksheets
A sheet for each month of the year to list occurences
An audit sheet for each month and
A couple of statistc sheets.

The monthly sheets list events numerically although the numbers are
not
consecutive.

I want to only have one audit worksheet which will search the monthly
sheets looking for the event that is numbered in cell C10.

The previous formula was

=if(isblank(c10),"",vlookup('Jan'!a2:t999,2,false) ).

How can I adapt this to look up all the monthly worksheets to find
the
event number? There are a number of formula in the audit sheet which
will read further information in the same row. Any assistance is
greatly appreciated.

stm


--
sonic-the-mouse-


--
sonic-the-mouse

Hi!

OK, this will work if the lookup tables on all the sheets have the same
layout.

Make a list of sheet names that need to be "searched". Say you put that list
in H1:H12. Give that range a name, something like SheetList.

The lookup value is entered in A1.

The lookup tables on all the sheets are in the range A1:C10.

This example looks up the value in column A and returns the corresponding
value from column 3 of the lookup table.

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=VLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRU E,COUNTIF(INDIRECT("'"&SheetList&"'!A1:A10"),A1)0 ,0))&"'!A:C"),3,0)

Biff

"sonic-the-mouse" wrote
in message ...

Sorry Biff for my lack of explanation.

I am trying to look up a number that could be on any one of the twelve
worksheets. I hope this clarifies what I'm looking for.

stm
Biff Wrote:
Hi!

It's not clear what you want.

Do you want to lookup a value that is on each sheet or do you want to
lookup
a value that may be on any sheet?

Biff

"sonic-the-mouse"
wrote
in message ...-

I have been tasked with streamlining a spreadsheet used at work. It
previously had 28 worksheets
A sheet for each month of the year to list occurences
An audit sheet for each month and
A couple of statistc sheets.

The monthly sheets list events numerically although the numbers are
not
consecutive.

I want to only have one audit worksheet which will search the monthly
sheets looking for the event that is numbered in cell C10.

The previous formula was

=if(isblank(c10),"",vlookup('Jan'!a2:t999,2,false) ).

How can I adapt this to look up all the monthly worksheets to find
the
event number? There are a number of formula in the audit sheet which
will read further information in the same row. Any assistance is
greatly appreciated.

stm


--
sonic-the-mouse-


--
sonic-the-mouse