Array formula using text

[spoursy]

Ive created a spreadsheet which acts as a rota for a number of staff.

In column A are the times of the shifts - for example A2 = '0600-1400', A3 = '1400-2200', A4 = '2200-0600'
in columns B,C,D,E,F,G,H (representing the days of each week (B=Monday, H=Sunday) are individuals names.

Thus if John is entered into B2, C3 and F4 this means that he is working Monday 0600-1400, Tuesday 1400-2200 and Friday 2200-0600

What I want to do is be able to enter a persons name in an dedicated audit cell and then have the spreadsheet tell me what days and shifts he is supposed to be working

any ideas??

[Richard Buttrey]

Hi,

Can I suggest you review the layout to something which will be more useful.

In column A record a date
In column B record the name of a person - use a data validation list to give you a drop down from which you can select
In column C record either a reference to a shift, e.g. 1, 2 or 3, or even the shift text (e.g. 0600-1400) again in a validation drop down.

Now you can use standard Excel filter functionality to see the individual's records.

This will be much more flexible