+ Reply to Thread
Results 1 to 8 of 8

Automatically change cell data according to given date

  1. #1
    Registered User
    Join Date
    04-27-2020
    Location
    Denmark
    MS-Off Ver
    Version 1908
    Posts
    4

    Automatically change cell data according to given date

    Hi all,

    I have followed this site for some time, and it seems you guys know an impressive vast amount about excel!!

    I have made an excel sheet that has to keep track of a range of cameras according to dates. So on the very left column the list of cameras are present, and in the top row the days and dates appear. I have already made it so, that the dates automatically change every day, so the second column to the left always show todays date. The general idea is then, that the sheet at all times will automatically update it self, and no manual changes will have to be done.
    BUT I simply do not know how to make the text entries in each column under each date 'move' to the next column on the left every day according to the date.
    Does somebody in here know how to do this, and thus help me immensely
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Automatically change cell data according to given date

    Can you attach the file? it'll be easier to see what you've done so far and provide solution.
    I'd like to know if the dates are changing with simple formulas like =Today() and adding a day in next column.
    From what you're describing, it seems like VBA will be required.
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  3. #3
    Registered User
    Join Date
    04-27-2020
    Location
    Denmark
    MS-Off Ver
    Version 1908
    Posts
    4

    Re: Automatically change cell data according to given date

    Thank you so much for taking the time to look into it! I have been away from work and could thus suddenly not get access to the file! I have though uploaded it here. Regarding the dates then it is exactly the way i have done it, and then to make saturday and Sunday (in Danish as per the file: lørdag and søndag) grey I used Conditional formating, but only for the first week so far until I know the correct way to solve this problem. I do not know what VBA is unfortunately?
    Attached Files Attached Files

  4. #4
    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
    79,368

    Re: Automatically change cell data according to given date

    This is a case of bad concept and design, I'm afraid. Manual entries will not move if the value of a formula-driven cell above it changes.

    As has already been mentioned, you would need VBA to do what you want (Visual Basic for Applications), which means having code and a macro-enabled workbook. Shall I move the thread for you?

    The other, easier option is to redesign your format and have new days appear on the right. Are you aware of the freeze panes function?
    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.

  5. #5
    Registered User
    Join Date
    04-27-2020
    Location
    Denmark
    MS-Off Ver
    Version 1908
    Posts
    4

    Re: Automatically change cell data according to given date

    I believe that making code and macro-enabled workbook will surpass my skills to a point where I will loose track of what I am doing :D but the idea of making new dates appear seems quite good! If that is an option then I would very much like to know how to do that? And the freeze panes function, I know yes, I used it so far for the far left column in order to have the data visible no matter the date. But can you help me with how to make the dates automatically appear, then I would be very grateful!?
    Last edited by Donsen; 04-30-2020 at 06:12 AM.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Automatically change cell data according to given date

    If I understand correctly then fill cell B4 with an actual date (i.e. 4/30/2020) and C4 and to the right with: =B4+1
    As you scroll to the right each day the text entries will stay wit the correct dates (i.e. Jane will appear for the dates 4/30/2020 through 5/2/2020)
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    04-27-2020
    Location
    Denmark
    MS-Off Ver
    Version 1908
    Posts
    4

    Re: Automatically change cell data according to given date

    Thank you very much for the help and guidance with the problem

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Automatically change cell data according to given date

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Automatically change cell based on date
    By scooterboygnu1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-27-2017, 07:45 AM
  2. Replies: 6
    Last Post: 04-24-2015, 03:16 AM
  3. I need cell to automatically change color based on date in different cell
    By acooley in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 08-27-2013, 04:55 PM
  4. [SOLVED] Macro to automatically change tab color to red base on a cell date
    By Chaunceycat in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-10-2012, 07:53 PM
  5. Automatically Change Sheet Tab Name With Date in Cell
    By chrisneu in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-31-2011, 11:34 PM
  6. Automatically change date when a cell value is changed
    By kdc1336 in forum Excel General
    Replies: 2
    Last Post: 08-12-2010, 12:09 AM
  7. Replies: 1
    Last Post: 04-03-2006, 12:10 AM

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