Formula checking multiple worksheets

  • Thread starter sonic-the-mouse
  • Start date
S

sonic-the-mouse

Biff

thank you for taking the time to write out the explanation of the
formula, some of it made sense to my limited knowledge.

=VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10)0,0))&"'!A:Z"),15,0)

I input the above formula as an array and excel offered to correct it
as below

=VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10)*0,0))&"'!A:Z"),15,0)

adding in the * near the add. Having accepted this the formula returns
#N/A
even though I have added data into the worksheets and asked to look up
something I know is there.

I know #N/A means that the formula cannot find the referenced data,
does this mean that I have entered the names of the monthly worksheets
wrong
(I tried entering them, by typing, as 'Jan'!, 'Feb'!, 'Mar'! etc). Is
there another way of entering the names of the worksheets?

Sorry about this but it is so frustrating as it appears so close to
completion

thanks again for any help
 
B

Biff

Hi!

This is the correct formula:

=VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10)>0,0))&"'!A:Z"),15,0)

The version you keep posting:

=VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10)0,0))&"'!A:Z"),15,0)

is missing the greater than > logical operator and that's why Excel see's it
as an error and wants to correct it. Here's what's really strange, I'm
replying to your post and in the previous quoted response from me the
formula is also missing the greater than > logical operator. But in my reply
that is the explanation of the formula, the > operator is there.

Let me try this. The portion of the formula that Excel wants to correct
should look like this:

A2:A999"),C10)>0,0))&"'!A:Z"),15,0)

After C10) and before 0 there should be the greater than operator >.
I know #N/A means that the formula cannot find the referenced data,
does this mean that I have entered the names of the monthly worksheets
wrong
(I tried entering them, by typing, as 'Jan'!, 'Feb'!, 'Mar'! etc). Is
there another way of entering the names of the worksheets?
Click to expand...

You shouldn't have to do anything to the sheet names. The single quotes and
exclaimation mark are used in a formula to let Excel know that refers to a
different worksheet.

If you still have problems I can put together a sample file that
demonstrates this technique. If you would be interested in that, post an
email address and I'll contact you.

Biff
 
D

Domenic

Biff said:
Here's what's really strange, I'm
replying to your post and in the previous quoted response from me the
formula is also missing the greater than > logical operator. But in my reply
that is the explanation of the formula, the > operator is there.
Click to expand...

We must be in the 'twilight zone'. :)
 
Top