2 into 1

Excel Discussion in 'Microsoft Office' started by Simont485, May 31, 2018.

  1. Simont485

    Simont485

    Joined:
    Feb 23, 2018
    Likes Received:
    10
    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.[​IMG]

    Any questions please ask and many thanks in advance!
     
    Simont485, May 31, 2018
    #1
    1. Advertisements

  2. Simont485

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    1,359
    Location:
    Manchester
    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?
     
    Becky, May 31, 2018
    #2
    1. Advertisements

  3. Simont485

    Simont485

    Joined:
    Feb 23, 2018
    Likes Received:
    10
    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..:)
     
    Last edited: May 31, 2018
    Simont485, May 31, 2018
    #3
    Becky likes this.
  4. Simont485

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    1,359
    Location:
    Manchester
    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.
     
    Becky, May 31, 2018
    #4
    Simont485 likes this.
  5. Simont485

    Simont485

    Joined:
    Feb 23, 2018
    Likes Received:
    10
    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!!!
     
    Last edited: Jun 1, 2018
    Simont485, Jun 1, 2018
    #5
  6. Simont485

    Simont485

    Joined:
    Feb 23, 2018
    Likes Received:
    10
    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)
     
    Simont485, Jun 4, 2018
    #6
    Becky likes this.
  7. Simont485

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    1,359
    Location:
    Manchester
    Sorry, I had a busy weekend and didn't get a chance to look at it. Glad to hear you found a solution though! :thumb:
     
    Becky, Jun 4, 2018
    #7
    Simont485 likes this.
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
Loading...