Roll numbers up in Excel Worksheet

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

Not open for further replies.
1. 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. Are the different values related in any regular way?

3. Not exactly sure what you're asking, but here goes. Each cell would have a number from .00 to 14.00.

4. 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. 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. 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. 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. 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. 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. 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.

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.

over 733,556 other people just like you!