+ Reply to Thread
Results 1 to 11 of 11

How do I have a cell shift over once it reaches a certain number?

  1. #1
    Registered User
    Join Date
    02-16-2018
    Location
    Fort Worth, Texas
    MS-Off Ver
    office 365
    Posts
    10

    How do I have a cell shift over once it reaches a certain number?

    I have a sheet for work that counts the number of days an account has been open with "=TODAY()-A35".

    What I need is a formula that can move the value from column to column when it meets certain days open (>30 days, >60 days, >90days)

    We already have it conditionally formatted to highlight yellow when the counter goes over the value limit on each column, then we move the equation over to the next and repeat 30 days later.

    Ultimately it needs to continue counting as it progresses in age until the account closes. Does anyone have any ideas? There are hundreds of these every week and we don't want to manually move them anymore.

  2. #2
    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,369

    Re: How do I have a cell shift over once it reaches a certain number?

    Welcome to the forum!

    I think you are going to need VBA, but let's have a look first.

    Will you please attach a sample Excel workbook?

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    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.

  3. #3
    Registered User
    Join Date
    02-16-2018
    Location
    Fort Worth, Texas
    MS-Off Ver
    office 365
    Posts
    10

    Re: How do I have a cell shift over once it reaches a certain number?

    I tried attaching the example excel file if it doesn't pop up immediately then I'll repost it after this.

    If you look at the file then you'll see a simple example of what we have. It's conditionally formatted to highlight when a range exceeds the columns specifications at which point someone has to go in and
    delete the cell and insert the next formula like on row 5 when C5 went over 30 and we had to insert D5.

    We want to have an equation that will cut C5 and insert D5 in a similar fashion.
    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,369

    Re: How do I have a cell shift over once it reaches a certain number?

    In C3 copied down:

    =IF(TODAY()-A3<30,TODAY()-A3,"")

    In D3 copied down:

    =IF(AND(TODAY()-A3>=30,TODAY()-A3<60),TODAY()-A3,"")

    In E3 coped down:

    =IF(AND(TODAY()-A3>=60,TODAY()-A3<90),TODAY()-A3,"")

    In F3 copied down:

    =IF(TODAY()-A3>=90,TODAY()-A3,"")

  5. #5
    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
    43,893

    Re: How do I have a cell shift over once it reaches a certain number?

    Try this in C3, copied across and down. No CF required.

    =IF(LOOKUP(TODAY()-$A3,{0,30,60,90},{"<=30",">30",">60",">90"})=C$2,TODAY()-$A3,"")
    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

  6. #6
    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,369

    Re: How do I have a cell shift over once it reaches a certain number?

    Much neater, Glenn - I'm getting tired!

  7. #7
    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
    43,893

    Re: How do I have a cell shift over once it reaches a certain number?

    You've been here a lot today!! At least I've been out and about footering around.

  8. #8
    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,369

    Re: How do I have a cell shift over once it reaches a certain number?

    Yes, I have. Last day of half term - back to normal next week!

  9. #9
    Registered User
    Join Date
    02-16-2018
    Location
    Fort Worth, Texas
    MS-Off Ver
    office 365
    Posts
    10

    Re: How do I have a cell shift over once it reaches a certain number?

    This works perfectly! Thank you so much for the help!

  10. #10
    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,369

    Re: How do I have a cell shift over once it reaches a certain number?

    I hope you used Glenn's version!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  11. #11
    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
    43,893

    Re: How do I have a cell shift over once it reaches a certain number?

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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. Replies: 3
    Last Post: 05-24-2017, 07:23 AM
  2. [SOLVED] Can you Play sound when a cell reaches a number like 12
    By gdclyde in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-26-2014, 03:04 PM
  3. changing a value of a cell when it reaches a certain number.
    By intotec in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-13-2013, 03:04 PM
  4. [SOLVED] How to subtract multiple cells from a cell and report soldout when main number reaches 0
    By QuietForce in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-02-2012, 01:43 AM
  5. [SOLVED] Need help adding two cells until one reaches a certain number
    By BWayne in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-23-2012, 12:26 AM
  6. Macro to Autofill number/formula until it reaches cell containing specific text
    By adamski911 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-07-2012, 12:33 PM
  7. Replies: 6
    Last Post: 03-30-2010, 11:11 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