Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Sep 2014
    Posts
    11
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Cell calculations

    Hi,

    I have a spreadsheet with date along the top column and as year progresses the column are filled in.

    What I would like to do is have a final column that calculates the difference between year start and the current position automatically.

    Hope I have explained myself

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi

    We need a little more info.
    How many columns are you using? 365??
    Are the dates entries going across by day? or by month?

    zeddy

  3. #3
    New Lounger
    Join Date
    Sep 2014
    Posts
    11
    Thanks
    7
    Thanked 0 Times in 0 Posts
    The heading will be by week so 52/53 column headings.

    On the rows will be namess

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi

    Still not sure what you require.

    Lets start with a sample file (see attached file)
    This sample file has 53 columns, for Week1 to Week53
    Enter a 'start date' in cell [C2]

    I chose a week-ending date of Fri 2-Jan-2015 as my start date.
    You could pick any date e.g. ending on a Sunday, or starting on a Monday etc. etc.
    Each subsequent week is just adding 7 days to this.

    Cell [BE2] has a formula which shows today's date.
    Formula in cell [BF2] then returns the appropriate week number.

    I added some random numbers as test data in the week columns.
    Then, a formula in column [BD] adds the total for weeks 1 up to the current week.
    Formulas in column [B] show year-to-date results.
    This is to allow the data to be shown (using windows-freeze-panes) as you scroll across through the weeks. (You could use vba to 'lock future weeks' to prevent data entry etc etc.)

    Using this file as a basis, perhaps you can tell us further what you are looking for.
    Or edit the file and post back etc etc.

    zeddy
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Sep 2014
    Posts
    11
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    thank you for that and its exactly what I am looking for bar I dont want the rows totalled up, just say the difference between the first and current week to be displayed in the split pane column.

    On my version of excel I could not see any formulas in the last two columns against each row?

    Thanks

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi

    ..there are no formulas in the last two columns, just the formulas in cells [BE2] and [BF2]

    See attached version2; I changed the formulas in column [BD] to subtract Week 1 value from current Week value.

    zeddy
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •