Yes I have followed your instructions and sorry Max but H8 in sheet X only
displays "Undefined" no matter what values I type in into
Y8,AA8,AB8,AC8,AL8,AW8,BJ8,BN8,BO8,BQ8,BR8.

"Max" wrote:

Try this on a spare copy of your file ..

Rename the source sheet (with the discontiguous range) as: X

Name a new sheet as: Y

In Y,

Put in A1: =X!Y8

Put in B1: =X!AA8
Copy B1 to D1

Put in E1: =X!AL8
Put in F1: =X!AW8
Put in G1: =X!BJ8

Put in H1: =X!BN8
Copy H1 to I1

Put in J1: =X!BQ8
Copy J1 to K1


Then in X,

we apply the formula in H8 to point to the continuous range A1:K1 in Y

Put in H8:
=IF(COUNTIF(Y!A1:K1,0)=11,"",IF(COUNTIF(Y!A1:K1,"R ")=11,"R",IF(COUNTIF(Y!A1:K1,"=85")=11,"HD",IF(CO UNTIF(Y!A1:K1,"=75")=11,"D",IF(COUNTIF(Y!A1:K1," =65")=11,"C",IF(COUNTIF(Y!A1:K1,49)=11,"NGP",IF(CO UNTIF(Y!A1:K1,"<=48")=11,"F","Undefined")))))))

Note that there's a slight change required for the 1st IF check, viz:
IF(COUNTIF(Y!A1:K1,0)=11,"", ...

instead of:
=IF(COUNTA(Y!A1:K1)=0,"", ...

The above change is required since the link formulas in Y!A1:K1 will return
zeros for blank source cells in X
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DavidB" wrote in message
...
Max

Is there any other way of doing this? Any thoughts on exactly why H8 is
not
reflecting what my formula is asking? And (sorry to ask this but...) how
do I
go about doing what you have suggested below? This is starting to become
complicated...I think....

Cheers
David