Indirect

[The Grim Discovery]

Excellers
I've been doing some formulating with the indirect function for pretty much the first time. It's good.

But I'm stumped as to how I can get Cell A10 in Sheet 1 to say 'Yes' if there is text populating cell A10 in any one or more of Sheet2:Sheet10


and for cell A10 in Sheet 1 to say 'No' if there is no text populating the exact same range. It seems like the kind of thing that can be done, only I can't do it.

Please help. You know you want to.

 

[Special-K99]

Re: INDIRECT help

Was thinking 3D Reference but cant seem to get it to work.
Maybe (regrettably)

=IF(ISTEXT(Sheet2!A10)+ISTEXT(Sheet3!A10)ISTEXT(Sheet4!A10)ISTEXT(Sheet5!A10)ISTEXT(Sheet6!A10)ISTEXT(Sheet7!A10)ISTEXT(Sheet8!A10)ISTEXT(Sheet9!A10)ISTEXT(Sheet10!A10),"Yes","No")

 

[The Grim Discovery]

Re: INDIRECT help

Thanks chap. It's doing what I need to do so nothing regrettable about that.
Just wondering - pushing it I know - if instead of producing 'yes' or 'no' the formula could report which sheet(s) contained the text. So if Sheet3!A10 contained text the output would be 'Sheet 3'.

 

[Special-K99]

Re: INDIRECT help

Identifying Sheet number just requires nested IFs

IF(ISTEXT(Sheet number!A10),value if true,IF(ISTEXT(Next sheet number!A10)...

So beginning

=IF(ISTEXT(Sheet2!A10),"Sheet2",IF(ISTEXT(Sheet3!A10),"Sheet3",ISTEXT(Sheet4!A10),"Sheet4"...

for each sheet you want to check until

...IF(ISTEXT(Sheet10!A10),"Sheet10","No text in any sheet"))))))))) <== 1 closing bracket for each IF condition

 

[The Grim Discovery]

Re: INDIRECT help

That's sweet. Thanks a big old bunch.