Please Help

[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.

 

[Gipy]

How could I implement this to have the matching cells appear in the relevant place on the timetable. So that if a pupil is matched with a particular support assistant on Mon Period 1 the pupil shows on the support assistants timetable in the B8 box and the support assistant is shown on the pupils timetable in B8 cell. It appears that the example you gave shows misses this key correlation. I think part of the problem is that the Excel file I was given had the named ranges set up and I'm not sure how this was done. I don't understand where to put the formula given in relation to the timetable layout I included in my link.

Thanks.

 

[markmzz]

How could I implement this to have the matching cells appear in the relevant place on the timetable. So that if a pupil is matched with a particular support assistant on Mon Period 1 the pupil shows on the support assistants timetable in the B8 box and the support assistant is shown on the pupils timetable in B8 cell. It appears that the example you gave shows misses this key correlation. I think part of the problem is that the Excel file I was given had the named ranges set up and I'm not sure how this was done. I don't understand where to put the formula given in relation to the timetable layout I included in my link.

Thanks.

Gipy,

Look at your PM.

Markmzz