+ 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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,397

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,397

    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 Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    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



  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,397

    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 Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,397

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,397

    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 Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    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