Formula - Find Cell and Reference another

[XCESIV]

I am building a simple roster builder that helps roster staff using previous requirements and sales history

The manager makes The roster by entering a " " (space) in each cell under each each staff members name.

These Cells are referenced above with time periods that the staff member is rostered to work

EG
Tom works 9am til 11am
The Spaced Cells are under the Cells C4 (the cell referencing 9am start), C5, C6, C7 (Being the last 30 min reference)

I need the system to be able to see that the first cell is C4 and the last being C7 and then in C2 display the Reference in A4 as the start time, and C3 showing the details in A7 as the finish time.

What would u suggest being the best wya.

Thanks

Warren

[deadlyduck]

Does this help:

To highlight the rostered hours, use conditional formatting as follows:

• Select all of the cells showing the work slots of each staff member
• Click "Format|Conditional formatting"
• Set the fields of the popup dialog as follows: Cell value > is equal to > " " {ie type a space between the inverted commas}
• Click the "Format" button and then the patterns tab. Select a colour you'd like the rostered times to show and presss OK twice to exit the popup

To display the Start time of the staff-member in cell C2, enter the following formula into C2:

Please Login or Register  to view this content.

In this code, A4:A8 represents the timeslots your company uses, so change the range reference to suit your needs so that all slots are referenced. C4:C8 is the range referencing a particular staff-member's time slots.

To display the end time for a particular staff-member in cell C3, you'll need to use a helper column at the extreme right of your table. This will show the time slots your company uses (like what you currently show in A4 down). Once you enter the C3 formula, you can hide this column so it doesn't display. Here's the code for C3:

Please Login or Register  to view this content.

In this code, C4 is the first time slot for a particular staff-member and E8 is the last time slot entered in the helper column o the right of your data.

Hope this helps and that you can follow the above!

[XCESIV]

Thank you for that help mate.

I have got the start time working. thats perfect. the end time i am lost with.
The forumula u gace for start time works fine for end times excect when i put a break into the shift.
and the it also depends when the break is to if it still works or now

This is Wat i have in E11 to display the end time so far

Please Login or Register  to view this content.

F2:BA2 Are the Finish Time Scale accross the top of the page
F10:BA10 are the time slots that is entered when a staff member is working.

Cells T10 to AI10 are filled in with a clear spot in cell AD11 which is the half hr break.

The Result should be referencing AI2 as this is the last cell, but it is referencing AD3 becuase of the break.

As you can see in the screen shot the start and finsih times are the same in Rows 10 and 11 yet the forula works with row 11 but not 10 and its all because of the location of the break.

I cant workout why tho.

Any ideas
thanks