Return worksheet name if condition is met

[Gipy]

Hi All,

I have a formula that returns the name of the current worksheet if any of the other worksheets have a B8 cell that equals TEST, this can be seen below:

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!"&CELL("address",B8)),"=TEST")),MID(CELL("filename"),FIND("]",CELL("filename"))+1,255),"NO")

This returns the name of the worksheet that holds this formula instead of the worksheet that has the B8 cell that has the text TEST in it.


I need the worksheet (or worksheets) name(s) returned for any of the worksheet(s) that meets this condition.

Any help would be much appreciated.

Thanks Gipy.

 

[tweedle]

Is MySheets not a named range containing the sheet name you want?

 

[Gipy]

Sorry my excel skills are not much good and I have inherited some of the code I posted from someone else.
Yes, I think the MySheets is a named range but not sure how I would fix the code I posted to get the name of the worksheet returned when a certain cell matches the TEST text.
It does seem to work in part but if the TEST text condition is met in multiple sheets then I would need each sheet name returned.
Hope I've explained this OK.

 

[T. Valko]

duplicate post:

http://www.mrexcel.com/forum/showthread.php?t=579236

 

[VoG]

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: http://www.mrexcel.com/forum/showthread.php?t=99490).

If you continue to exhibit this behaviour you will be suspended or banned.