=IF(NOT(ISNA(VLOOKUP Function
 

I am using Excel 2003

I am trying to produce a summary shett from five differnet worksheets.

I am using the following function
=IF(NOT(ISNA(VLOOKUP(E7,Tests!D1:Tests!S147,18,FAL SE))),VLOOKUP(E7,Tests!D1:Tests!S147,18,FALSE),"") .

It works fine until I reach 18 and above and it returns #REF!.

Does this formula not work after 17 or is there another formula that I
should be using.

Colin

=IF(NOT(ISNA(VLOOKUP Function
 

Hi,

The VLOOKUP() table array cannot span across sheets. Do let us know what
you are trying to do?

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"CWH" wrote in message
...
I am using Excel 2003

I am trying to produce a summary shett from five differnet worksheets.

I am using the following function
=IF(NOT(ISNA(VLOOKUP(E7,Tests!D1:Tests!S147,18,FAL SE))),VLOOKUP(E7,Tests!D1:Tests!S147,18,FALSE),"") .

It works fine until I reach 18 and above and it returns #REF!.

Does this formula not work after 17 or is there another formula that I
should be using.

Colin

=IF(NOT(ISNA(VLOOKUP Function
 

It should not work for 17 either because the range D to S is only 16 columns.
Your formula should also look like this.

=IF(NOT(ISNA(VLOOKUP(E7,Tests!D1:S147,16,FALSE))), VLOOKUP(E7,Tests!D1:S147,16,FALSE),"").


"CWH" wrote:

I am using Excel 2003

I am trying to produce a summary shett from five differnet worksheets.

I am using the following function
=IF(NOT(ISNA(VLOOKUP(E7,Tests!D1:Tests!S147,18,FAL SE))),VLOOKUP(E7,Tests!D1:Tests!S147,18,FALSE),"") .

It works fine until I reach 18 and above and it returns #REF!.

Does this formula not work after 17 or is there another formula that I
should be using.

Colin