I have attached a file
I want to auto generate Row Numbers R=1 to 180 max
Then, another 2 Rows, which Shift Row R, Up and Down, by S=0 to 12 max
Example : R=9, S=4
Rows Up Down
1 5 6
2 6 7
3 7 8
4 8 9
5 9 1
6 1 2
7 2 3
8 3 4
9 4 5
I have attached a file
I want to auto generate Row Numbers R=1 to 180 max
Then, another 2 Rows, which Shift Row R, Up and Down, by S=0 to 12 max
Example : R=9, S=4
Rows Up Down
1 5 6
2 6 7
3 7 8
4 8 9
5 9 1
6 1 2
7 2 3
8 3 4
9 4 5
Hi ionelz,
I'm not sure this is exactly what you want, as I don't understand the "up down". See if you can work with this file.
Auto Rows Number.xlsx
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Horizontal is easy to explain
1 2 3 4 5 6 7 8 9
Shift Right 4, mean 6 7 8 9 1 2 3 4 5
4 number are pushed out to right
1 shift 4 to right which push 6 7 8 9 at beginning
It is a CIRCLE, at end of 9 is 1
Shift Left 4, mean 5 6 7 8 9 1 2 3 4
4 numbers, 1 2 3 4 is pushed out to left.....
Last edited by ionelz; 02-16-2019 at 09:39 PM.
Yep,
Seems like you need the Mod() function with some combination of plus or minus your shift number. See my example and work on the starting number in row 2.
file attached again, with new condition
Hi ionelz,
Find the attached with the same formulas as post #2. This solves your problem. Change the R and S numbers and see it work like you want.
Mod Up Down.xlsx
First, I want auto generate row numbers, from 1 to B4
Otherwise, 24 is always there
If B4=50 ?
Hi ionelz,
I must be missing something. I thought my answer was really good and exactly what you asked for. I though I was given the row numbers. See my first file in post 2 where I have a formula for Row numbers. I think it was =Row()-1 . Then I pulled that formula down as many rows as you needed. Doesn't this solve your question?
I may not explained well.
I need to have DYNAMIC number of Rows.
I will drag the formula for rows to max 180 ( I will protect for that space) but I need to show ONLY R rows
Then I want to Shift them, Up and Down, per post #5
Will you allow VBA to create the first column? Can you give an example of how your steps are created and what you get first and then what is to be done from there?
I guess I'm just dense in what you are trying to solve/accomplish with a Dynamic number of rows. I don't see why you can't just pull down the correct number in column A in the beginning.
Sorry I'm not getting it....
Marv
A3 =24
A4 =6
Please try
E2
=IF(D3>$A$3,"",MOD($D3+$A$4-1,$A$3)+1)
F2
=IF(E3>$A$3,"",MOD($D3-$A$4-1,$A$3)+1)
Drag E2:F2 down
Of course, like always !
Done !
Thank you again !
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks