Linking entered dates to column location

  • I have a spreadsheet with the following format. I was given this spreadsheet to use so I'm not sure what use these formulas for dates have in opposition to normal text.
    Along the first 4 rows of each column:
    K2: 2017 [=TEXT(K$5,"yyyy")]
    K3: April[=TEXT(K$5,"mmmm")]
    K4: Wed [=TEXT(K$5,"ddd")]
    K5: 05 [=J5+1]
    In another sheet, I want to do:
    =Sum(K6:N6)
    =Sum(K7:N7)
    At the start of every week I have to change the columns to the dates of that week
    =Sum(O6:S6)
    =Sum(O7:S7)
    The first half is Sheet1 and goes over the whole year, the second half you can see in the formula bar I have to update those formulas weekly, manually looking at which column in sheet1 lines up with the dates I need. I made a small orange box However I don't know anyway to actually link it so that I can just change the date or column in that box and it will change all the formulas.


    [ATTACH=CONFIG]72211[/ATTACH]

  • Re: Linking entered dates to column location


    Duplicate thread closed. One thread per issue only, please. Continue here: http://www.ozgrid.com/forum/showthread.php?t=203784

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!