+ Reply to Thread
Results 1 to 6 of 6

Updating weekly movement for a range of Data dynamically. Help required.

  1. #1
    Registered User
    Join Date
    06-19-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    4

    Updating weekly movement for a range of Data dynamically. Help required.

    Dear all,

    I have a table which is driven by a date selected via a data validation list (of Dates). Is it possible when a user selects a particular date, Excel would automatically compare the sum of three age groups ( 5-10 years, 11-15 years, 16-20 years) in table "Cinema: Age group Attendance" for that date and compare it with similar age groups for the previous week listed in the table.

    Once compared, Excel should automatically give me the movement in terms of up, down or no change. (Please see cell f6 in the spreadsheet attached).

    Will really appreciate your response.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Updating weekly movement for a range of Data dynamically. Help required.

    HI ak13,

    I have provided below formula in cell F6 :-

    Book2.xlsx

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    06-19-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Updating weekly movement for a range of Data dynamically. Help required.

    Hi Dilip,

    Thanks for replying and apologies for the delay from myself. You have calculated movement with the target(Cell D6). However, based on the date value in cell c3, I want Excel to automatically calculate the sum of age ranges 5-10,11-15 & 16-20 years of the previous week and then calculate upward,downward or neutral movement in cell F6. I will really appreciate if you can help me with that.

    Thanks in advance.

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Updating weekly movement for a range of Data dynamically. Help required.

    However, based on the date value in cell c3, I want Excel to automatically calculate the sum of age ranges 5-10,11-15 & 16-20 years of the previous week
    This is what you are doing in cell C6 and I used that
    Why you want a duplicate formulation effort just for indicators in F6 ?

    Regards,
    DILIPandey
    <click on below * if this helps>

  5. #5
    Registered User
    Join Date
    06-19-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Updating weekly movement for a range of Data dynamically. Help required.

    Hi Dilip, thanks for your reply again. I think I am not explaining very well. Lets take an example. If you select date 13/06/13, the actual figures in cell c6 should change to 133and this should be an upward movement (windging 3 "p") in cell f6, as in the previous week the sum of range was just 83. But in your spreadsheet it is showing a downward movement as it is comparing against the target, which I do not want. I want Excel to compare it (13/06/13) with 6/06/13 (prior week) automatically, calculate the total from range 5-19,11-15& 16-20 years and then calculate the movement in cell F6. I hope I have explained myself better. Thanks.

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Updating weekly movement for a range of Data dynamically. Help required.

    Okay.. used below formula to obtain previous week's data based on selected week :-

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Book2.xlsx

    Regards,
    DILIPandey
    <click on below * if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1