LinkBack URL
About LinkBacksHi, I need to be able to number every other row on my spread sheet. The
numbers must be 1,2,3 ect. but I need to skip a row inbetween each. Is this
possible? Thanks
Hi, I need to be able to number every other row on my spread sheet. The
numbers must be 1,2,3 ect. but I need to skip a row inbetween each. Is this
possible? Thanks
Try something like this:
A1: Blank
A2: =IF(ISNUMBER(OFFSET(A2,-1,0)),"",MAX($A$1:OFFSET(A2,-1,0))+1)
copy that formula down as far as you need.
It will number every other cell, beginning with 1 in A2.
If you enter a number in A1, the numbering will resequence.
Also, you can insert rows without throwing off the sequencing.
Experiment with it, changing references to suit your situation.
Does that help?
***********
Regards,
Ron
XL2002, WinXP-Pro
"KGB" wrote:
> Hi, I need to be able to number every other row on my spread sheet. The
> numbers must be 1,2,3 ect. but I need to skip a row inbetween each. Is this
> possible? Thanks
Hi
One way
Enter a 1 in A1, 2 in A3.
Mark cells A1:A4.
Grab the fill handle (the small black cross that appears as you hover
over the bottom right corner of cell A4) and drag down as far as you
need.
--
Regards
Roger Govier
"KGB" <[email protected]> wrote in message
news:[email protected]...
> Hi, I need to be able to number every other row on my spread sheet.
> The
> numbers must be 1,2,3 ect. but I need to skip a row inbetween each. Is
> this
> possible? Thanks
Using column A. In A1 enter:
=IF(ISEVEN(ROW()),"",(ROW()+1)/2)
and copy down
--
Gary's Student
"KGB" wrote:
> Hi, I need to be able to number every other row on my spread sheet. The
> numbers must be 1,2,3 ect. but I need to skip a row inbetween each. Is this
> possible? Thanks
Hello Ron,Originally Posted by Ron Coderre
If you are still here in this forum (the post is rather old), please, explain how this formula works. I am not that good with excel yet, and this formula does wonders. Please, if possible, guide me through it so I can learn and understand it.
Thank you very much!
Welcome to the forum Vitalite.
It's great that you want to learn more, but please also take a moment to read the Forum Rules, particularly Rule 2: Do not post a question in the thread of another member -- start your own thread. If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
You noted yourself that this is an old thread. Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Don't worry this once. I'll answer you to save Ron the time (he is still on here - he's one of the moderators).
Formula:
Please Login or Register to view this content.
OFFSET looks above/right/below/left of the cell address specified, depending on what's input. In this case, OFFSET(A2,-1,0) means 'look one row up (that's the -1) in the same column (that's the zero). So from A2, it's looking at A1. As it's dragged down, it keeps looking one cell up, so from A10 it'll look at A9, for example.
ISNUMBER checks if the cell being looked at is a number.
IF checks if the first statement is true and does one action if it is, another if it isn't: IF(this, do this, otherwise do this). So IF(ISNUMBER(OFFSET(A2,-1,0)),"", will return blank ("") if the cell above (A1 in this case) is a number. If it's not a number, the IF statement goes on to the 'otherwise do this' part.
MAX finds the highest number in a specified range. The range in this case is from $A$1 (the $ signs hard-code this to A1, so it won't change as the formula is dragged down) to the Offset cell, as discussed above. So, for example, once this is dragged down to A10, this becomes MAX(A1:A9).
The +1 at the end of the formula is probably self-explanatory: it adds one to the previous highest number (from the MAX).
So, in plain English (I hope), the formula is:
If the cell above this one is a number, make this cell blank, otherwise add one to the highest number above this cell.
Hope that helps.
Last edited by Aardigspook; 05-22-2017 at 02:51 PM.
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
Aardigspook, Thank you very much for your great help, and I do apologize for writing in the old thread - different forums have different rules, and I was once told that is not a good practice to start a new thread if one is referring to the existing one. I will follow your recommendations.
Thank you once again for taking time and explaining to me how the function works! :-) Much appreciated!
PS: Truly sorry for not replying earlier; tough time.
Last edited by Vitalite; 05-31-2017 at 11:28 AM.
You're welcome, thanks for the feedback and the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks