Cell references

[Gary Thomson]

I would like to be able to do the following:

There will always only be ONE "a" in the following range:

UNAVAILABILITY
A B C D E ....
1 Room 1-Feb 2-Feb 3-Feb 4-Feb ....
2 Maths
3 English ab b
4 Music bc b bcde
5 P.E.
6 Geography
7 History
8 Drama
9 Science

In the case above, it in in cell C3.

How would I return the cell reference for this value?

There is another sheet, "USAGE", which has a similar set
up of Rooms and Dates, and I want something like the
following:
=if(Usage!(cell in which "a" was found in "UNAVAILABILITY")
="x",1,0)

i.e. Excel would find "a" in cell C3 in the UNAVAILABILITY
sheet, then check if there was an "x" in cell C3 of the
USAGE sheet.


What if there were TWO "a" in the range? i.e.

UNAVAILABILITY
A B C D E ....
1 Unit 1-Feb 2-Feb 3-Feb 4-Feb ....
2 Maths a
3 English ab b
4 Music bc b bcde
5 P.E.
6 Geography
7 History
8 Drama
9 Science a

How would I return BOTH cell references?

What if there were 3? 4? (I don't need to know what to do
if there were 5, as I don't need to implement this).

Thanx

 

[David Hager]

Use a conditional format formula of =FIND("a",B2) for the table with B2 as
the upper left cell.

 

[Gary Thomson]

David,

Could you please describe the answer you gave me in more
detail as I don't quite know what it means.

Sorry!

Gary.

 

[David Hager]

1. Highlight your table, starting at B2.
2. Select Format, Conditional Formatting from the menu.
3. For Condition1, selct Formula Is
4. Type =FIND("a",B2) in the input area to the right.
5. Select a formatting option with the Format button.
6. Click the OK button.

Cells containing an "a" will be formatted as per your selection.