Excel 2 into 1

[Simont485]

I have access to 2 excel based staff shift rotas.

In rota 1 the names are in cells A4:A25 The shift ranges are in J4:NQ4 for each person
In rota 2 the names are in A4:A11 The shift ranges are J4:NQ4

I want to create one spreadsheet from the 2 rotas.

So on my spreadsheet I want it to have the following

In column A I will have a list of names.

In column B there is an overtime count

In cell D1 I want to input a date and then under that date show the contents of the cells corresponding to the names in column A.

In cell C1 I want the previous days date and then the corresponding contents as above.

I have columns A & B sorted.

Its column D and the contents I am having problems with.

I have used =Left(range from Rota1, 1) as I only want the left hand character in my spreadsheet.. That works ok but I need to have the comments carried over and
all other formatting too if possible and
how to make it work for any date....



Unfortunately I am unable to upload an example as I am doing this all at work and not allowed to upload to external sites.

Any questions please ask and many thanks in advance!

 

[Becky]

I don't understand what you mean by getting D2 to show the contents of the cells that correspond to the names - in what way do they correspond? If column A is names and column B is overtime, where does the date come into it?

 

[Simont485]

I don't understand what you mean by getting D2 to show the contents of the cells that correspond to the names - in what way do they correspond? If column A is names and column B is overtime, where does the date come into it?

SO trying to keep it simple.
I have a list of names with shifts next to them and the column of shifts has a date at the top. So there is a shift for each date for each person from A3:NQ3 which scrolls across the screen for a whole year......

On a separate sheet I want to show the staff names in a list. I then want to input a date in the next column and then below that date show the shifts for those staff for that date only......

so instead of having to scroll across to find the date etc excel will show the shifts under that date.

Main rota exaple
Row 1 has dates (C1:NQ1)
Column A has names (A2:A10)
Columns C2:NQ10 has the shifts

New Spreadsheet
Column A has names (A2:A10)
Cell C1 input date
Cells C2:C10 shifts for the list of names on that date from Main Rota.

In C2:C10 I only need to see the left character.

Does that make any more sense...if not Id need to send you an excel example....somehow..

 

[Becky]

Ah ok I think I getcha now, thanks for the clarification I'm running out of time today but I'll try and take a look tomorrow.

 

[Simont485]

Ah ok I think I getcha now, thanks for the clarification I'm running out of time today but I'll try and take a look tomorrow.

ON spread sheet 1
I have a row of dates lets say B1:AB1
In A2:A8 I have a list of names.
In the cells B2:AB8 are letters representing shifts

On Spreadsheet 2
I have the same list of names
In B1 I want to enter a date the matches one of the dates on spreadsheet 1
in B2:B8 I want to see the left most letter that appear under that date in spreadsheet 1

Basically one Column of shifts under one date instead of hunting for one date out of 365 columns of shifts

It starts out so easy then gets more and more complex!!!

 

[Simont485]

This does it!!!

=IFERROR(LEFT(INDEX(Sheet1!$B$3:$M$20,MATCH($A17,Sheet1!$A$3:$A$20,0),MATCH(B$4,Sheet1!$B$2:$M$2,0)),1),"")

Right onto next issue!! (involves VBA)

 

[Becky]

Sorry, I had a busy weekend and didn't get a chance to look at it. Glad to hear you found a solution though!