# Roll numbers up in Excel Worksheet

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

Not open for further replies.

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

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

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

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?

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

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

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

Joined:
Apr 7, 2007
Messages:
439
If so this is fairly easy. But I'm not sure why you would want this, or if this is what you require.

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

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.

As Seen On