Checking a number of sheets

[sphenisc]

Hi,

I've a number of sheets. The 1st cell in the C column is blank on all
the sheets except one. The same is true for the next cell, except the
value may be on a different sheet.
For example with sheets 1 to 3
.....sheet 1.....sheet 2....... sheet 3
C1........................................4
C2.....................3..................
C3........................................2
C4....1...................................

etc....

I hope you get the idea.

I've also got a summary sheet which I want to return a (different)
value from the sheet which happens to have a number on it (from M1).

I've got the formula below which works for 3 sheets, but I'd like
something which I can extend to any number of sheets.


=IF(ISBLANK(Sheet1!C7),IF(ISBLANK(Sheet2!C7),IF(ISBLANK(Sheet3!C7),"error",Sheet3!M$1),Sheet2!M$1),Sheet1!M$1)

Any suggestions welcome!

Cheers

 

[Domenic]

Let A1:A3 contain Sheet1, Sheet2, and Sheet3

Since only one sheet will ever have a value for C7, if Column M contains
numerical values, try

=SUMPRODUCT(--(N(INDIRECT("'"&A1:A3&"'!C7"))<>""),N(INDIRECT("'"&A1:A3&"'
!M7")))

If Column M contains text values, try...

=INDEX(T(INDIRECT("'"&A1:A3&"'!M7")),MATCH(TRUE,N(INDIRECT("'"&A1:A3&"'!C
7"))<>"",0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the range
for your sheet names accordingly.

Hope this helps!