+ Reply to Thread
Results 1 to 11 of 11

Retain Value After Date Changes

  1. 04-28-2023, 10:17 PM #1
    Kumara_faith
    Kumara_faith is offline
    Forum Contributor
    Join Date
    08-19-2006
    MS-Off Ver
    Excel 365
    Posts
    229

    Retain Value After Date Changes

    Hi,

    I have the sales figures which are updated daily in Sheet 1. This figures in Sheet 1 are updated daily and once we cross over to the next day, it overrides the figures from previous day to the current date.

    In Sheet 2, I am trying to sum the sales figures automatically from Sheet 1 for every day. The formula should retain the values when we cross over to the next day and so forth.

    Is there a way to build a formula to accomplish this ? Appreciate all the help.

    I have a similar cross post here:

    https://www.mrexcel.com/board/thread...value.1235119/
    Attached Files Attached Files
    Last edited by Kumara_faith; 04-28-2023 at 10:19 PM.
    Reply With QuoteRegister To Reply
  2. 04-29-2023, 02:41 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Retain Value After Date Changes

    Maybe:

    =INDEX(Sheet2!$C$3:$F$60,MATCH($C$2,Sheet2!$B$3:$B$60,0),MATCH(C4,Sheet2!$C$2:$F$2,0))
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 04-29-2023, 02:42 AM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Retain Value After Date Changes

    If not, show your expected answers on your file: you did not explain what you meant by "retain the values".
    Reply With QuoteRegister To Reply
  4. 04-29-2023, 02:46 AM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Retain Value After Date Changes

    An alternative explanation might be:

    =SUMIF(Sheet2!$B$3:$B$100,"<="&Sheet1!$C$2,INDEX(Sheet2!$C$3:$F$100,,MATCH(C4,Sheet2!$C$2:$F$2,0)))
    Reply With QuoteRegister To Reply
  5. 04-29-2023, 03:50 AM #5
    Kumara_faith
    Kumara_faith is offline
    Forum Contributor
    Join Date
    08-19-2006
    MS-Off Ver
    Excel 365
    Posts
    229

    Re: Retain Value After Date Changes

    Hi Glenn,

    Actually, the system data will be in Sheet 1. This is the set of data that will change everyday. In other words, when we cross over to the next day, the system will override the data in Sheet 1 to the current date data. Sheet 2 is actually the summary that we are trying to build the formula to extract the data from Sheet 1.

    Example, say for 2 Jan 2023, New York had a sales of 500. Therefore, in Sheet 2, the data should populate 500 for 2 Jan 2023. When we cross over to 3 Jan 2023, the data might change say for 300 for NY and the previous data will be overwritten by the system for 2 Jan 2023 in Sheet 1.But the formula in Sheet 2 for NY for 2 Jan 2023 should retain the value of 500 for 2 Jan 2023. This should be result for daily basis where the previous day data from Sheet 1 is retained in Sheet 2. Hope this clarifies. Do let me know if you need any further clarification.
    Reply With QuoteRegister To Reply
  6. 04-29-2023, 03:51 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: Retain Value After Date Changes

    You will need VBA for this. Shall I move the thread for you?
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  7. 04-29-2023, 03:56 AM #7
    Kumara_faith
    Kumara_faith is offline
    Forum Contributor
    Join Date
    08-19-2006
    MS-Off Ver
    Excel 365
    Posts
    229

    Re: Retain Value After Date Changes

    Hi Ali,

    Apologies. I wasn't aware this could only be accomplished via VBA.

    Yes, you may move the thread. Thanks
    Reply With QuoteRegister To Reply
  8. 04-29-2023, 04:07 AM #8
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: Retain Value After Date Changes

    That's done.
    Reply With QuoteRegister To Reply
  9. 04-29-2023, 08:00 PM #9
    Kumara_faith
    Kumara_faith is offline
    Forum Contributor
    Join Date
    08-19-2006
    MS-Off Ver
    Excel 365
    Posts
    229

    Re: Retain Value After Date Changes

    Hi,

    Appreciate any help with the solution. Thanks. 🙏
    Reply With QuoteRegister To Reply
  10. 04-30-2023, 08:45 PM #10
    Kumara_faith
    Kumara_faith is offline
    Forum Contributor
    Join Date
    08-19-2006
    MS-Off Ver
    Excel 365
    Posts
    229

    Re: Retain Value After Date Changes

    Hi,

    Appreciate any help with the solution. Thanks.
    Reply With QuoteRegister To Reply
  11. 04-30-2023, 09:23 PM #11
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,525

    Re: Retain Value After Date Changes

    To sheet1 code module
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to Retain the same date in a total hours when the day shifts to a different date
    By Mondo0906 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-04-2020, 02:09 AM
  2. [SOLVED] Date formats don't retain their format?
    By RustyNail in forum Excel General
    Replies: 4
    Last Post: 02-13-2019, 05:28 AM
  3. Need date field to update when data entered, but retain that date
    By ELIVERGARA in forum Excel General
    Replies: 2
    Last Post: 10-13-2014, 02:11 PM
  4. Retain date format for input entered as date
    By Chippi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-24-2014, 07:20 AM
  5. Find a Min Value and retain the date adjacent to the Min Value
    By alghareeb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-26-2014, 04:27 AM
  6. [SOLVED] Retain date format in cell
    By Jan 2HW in forum Excel General
    Replies: 1
    Last Post: 08-09-2006, 02:34 PM
  7. Excel retain data and date
    By Robb @ FLW in forum Excel General
    Replies: 1
    Last Post: 12-14-2005, 05:00 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1