1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Roll numbers up in Excel Worksheet

Discussion in 'Business Applications' started by george4, Oct 12, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. george4

    george4 Thread Starter

    Joined:
    Oct 12, 2008
    Messages:
    4
    I need to put a number into cell A7, and the previous number that was in A7 rolls up to A6, and any number in the above cells to roll upwards also, with A1 rolling off of the page.

    How would I do this?
     
  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Are the different values related in any regular way?
     
  3. george4

    george4 Thread Starter

    Joined:
    Oct 12, 2008
    Messages:
    4
    Not exactly sure what you're asking, but here goes. Each cell would have a number from .00 to 14.00.
     
  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    If the values were related in a regular way, for example, A1=A2*2, and A2=A3*2, I could (maybe) write a formula that would make the figures change as you entered new data in the column.
    Are the values real numbers or the results of formulas? Probably some VBA guru here could figure out a way to help you, but they will need to know the range of cells in play - are only some rows going to hold values, or all of them?
     
  5. george4

    george4 Thread Starter

    Joined:
    Oct 12, 2008
    Messages:
    4
    They are real numbers. They only go from A1 thru A7 just like in the original question...that's not just an example, it's the whole thing. What happens after that is simple math that I can do. It's the being able to input the new number into A7, which may or may not already have a number in it, and have the rest of the numbers move up the column and the one in A1 disappear.

    Then this would make a new total in A8.
     
  6. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    So, basically, if the first day it was
    1
    2
    3
    4
    5
    6
    7
    28
    the next you would want
    2
    3
    4
    5
    6
    7
    8
    35
    ?
    Can't think of a way to do this with formulas, but maybe with VBA - I'll ask someone.
    Probably will have to actually enter the data into another cell and have it read from there.
     
  7. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Hi george4,

    Unfortunately, your description is a little vague.

    Can you explain it a little more, and in simpler terms?

    Better, would be to attach an Excel sheet (minus any sensitive data), with your current sequence of numbers, and then what you would like to see as the result. The basics of your question seems possible, but we need to know, what the expected result should be, what the current numbers are, and sequence that gives the result. I.e. Increments by 1, increments depending on Row / Column number, or a calculated formulae.
     
  8. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Reading your description again, is slurpee55's assumption correct?
    If so this is fairly easy. But I'm not sure why you would want this, or if this is what you require.
     
  9. george4

    george4 Thread Starter

    Joined:
    Oct 12, 2008
    Messages:
    4
    Actually, in the top set of numbers in your example it would go down to 6 then the next number would be 28. Then in the lower example it would go down to 5 then 28 then 35. 2,3,4,5,6,28,35.

    If the next entry were 42 it would be, from top to bottom, 3,4,5,28,35,42.
     
  10. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    This VBA procedure, will give you the basics, of what you've asked for, if you want more then just that, then let me know. Personally I would add a check to see if a new value has been added before moving everything up, but I need parameters, as to what to validate against.

    Code:
    Option Explicit
    
    Sub RollNumUp()
    ' Basic procedure for moving each value up
    ' Based on A1 being the starting cell, and all values in range (xlDown) from A1.
        Dim Cell As Variant
        Dim rg As Range
        
        If IsEmpty(Range("A1")) Then
            'Do Nothing
        ElseIf IsEmpty(Range("A1").Offset(1)) Then
            Set rg = Range("A1")
        Else
            Set rg = Range("A1", Range("A1").End(xlDown))
            For Each Cell In rg
                Cell.Value = Cell.Offset(1, 0).Value
            Next Cell
        End If
    End Sub
    
    You can manual change the starting cell, or I can change it to use a selected range, depends on your requirement.
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/758568

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice