Column Numbering

A

Andy M. - Utah

Ok, I'm sure this can be done and it might have already been posted but this is what I'm wanting to do. I have a spreadsheet of sports cards of a particular player that I collect. I'm wanting to have a column of number, 1,2,3,4,5,..... etc so I'll know how many cards of that particular player I have. Now here is my problem. Because my spreadsheet is alphabetized and also is done by year, starting with 1989 when the player was drafted, anytime I get a new card I insert a row into the spreadsheet to input the card information. So what I want it to do when I insert a new row is re-number all the columns. So in other words if I insert a row between #3 and #4. #4 should be card #5 now, and so on down the column. Can this be done? Thanks!
 
R

Ragdyer

Don't know if I exactly understand what you're looking for, since you are
mentioning numbering rows *and* columns.

Would it help to use XL's row numbers to number your data list rows, where
an inserted *new* row would also increment your list's rows.

=ROW()

This could be adjusted, if your starting data list row was not in row 1.
For example, if you start at row 5:
=ROW()-4

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Andy M. - Utah said:
Ok, I'm sure this can be done and it might have already been posted but
Click to expand...
this is what I'm wanting to do. I have a spreadsheet of sports cards of a
particular player that I collect. I'm wanting to have a column of number,
1,2,3,4,5,..... etc so I'll know how many cards of that particular player I
have. Now here is my problem. Because my spreadsheet is alphabetized and
also is done by year, starting with 1989 when the player was drafted,
anytime I get a new card I insert a row into the spreadsheet to input the
card information. So what I want it to do when I insert a new row is
re-number all the columns. So in other words if I insert a row between #3
and #4. #4 should be card #5 now, and so on down the column. Can this be
done? Thanks!
 
D

dave

I'd recommend a different approach. I'd use a summary
table to the right of your list or on the next tab which
has a column of player names which you've collected in
column A(assumes new tab(sheet) for table) and a formula
called "countif" in column B which says, lookup in the
previous tab's table how many of each player(linked to
column A) you've collected. Sumif([range],[criteria-
link this to the player named in column A]).

As long as you name the entire column as your range, you
can sort as many times as you want and you wont need a
separate column to total up your cards.

hth,
Dave



-----Original Message-----
Ok, I'm sure this can be done and it might have already
Click to expand...
been posted but this is what I'm wanting to do. I have a
spreadsheet of sports cards of a particular player that I
collect. I'm wanting to have a column of number,
1,2,3,4,5,..... etc so I'll know how many cards of that
particular player I have. Now here is my problem.
Because my spreadsheet is alphabetized and also is done
by year, starting with 1989 when the player was drafted,
anytime I get a new card I insert a row into the
spreadsheet to input the card information. So what I
want it to do when I insert a new row is re-number all
the columns. So in other words if I insert a row between
#3 and #4. #4 should be card #5 now, and so on down the
column. Can this be done? Thanks!
 
S

Stephen Dunn

It isn't very clear what you want here. Have you tried turning on R1C1
style referencing?

Tools>Options>General>R1C1 reference style

Does this help?



Andy M. - Utah said:
Ok, I'm sure this can be done and it might have already been posted but
Click to expand...
this is what I'm wanting to do. I have a spreadsheet of sports cards of a
particular player that I collect. I'm wanting to have a column of number,
1,2,3,4,5,..... etc so I'll know how many cards of that particular player I
have. Now here is my problem. Because my spreadsheet is alphabetized and
also is done by year, starting with 1989 when the player was drafted,
anytime I get a new card I insert a row into the spreadsheet to input the
card information. So what I want it to do when I insert a new row is
re-number all the columns. So in other words if I insert a row between #3
and #4. #4 should be card #5 now, and so on down the column. Can this be
done? Thanks!
 
R

RagDyer

Have you tried any of the suggestions?
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Hmmm, I thought I was pretty clear. So imagine an excel spreadsheet that
has 600 + sports cards of one player, and they are numbered on the left side
1-650. I buy another card and my list is alphebetized and chronological.
So I get a 1995 Topps card or something, I'm inserting it between #57 and
#58. As I said in my earlier post, that 1995 Topps card now becomes card #
58, and the next then I want Excel to re-number all of the cards below that.
I am not very technical savvy (sp?), but I'm hoping there is an easy way of
doing this. I hope this makes more sense......

Andy M. - Utah said:
Ok, I'm sure this can be done and it might have already been posted but
Click to expand...
this is what I'm wanting to do. I have a spreadsheet of sports cards of a
particular player that I collect. I'm wanting to have a column of number,
1,2,3,4,5,..... etc so I'll know how many cards of that particular player I
have. Now here is my problem. Because my spreadsheet is alphabetized and
also is done by year, starting with 1989 when the player was drafted,
anytime I get a new card I insert a row into the spreadsheet to input the
card information. So what I want it to do when I insert a new row is
re-number all the columns. So in other words if I insert a row between #3
and #4. #4 should be card #5 now, and so on down the column. Can this be
done? Thanks!
 
S

Stephen Dunn

Ahh, now I see it. Still had to read this a couple of times before it
clicked though. (Must admit that's not exactly unusual for me...)

You need Ragdyer's answer.


Andy M. - Utah said:
Hmmm, I thought I was pretty clear. So imagine an excel spreadsheet that
Click to expand...
has 600 + sports cards of one player, and they are numbered on the left side
1-650. I buy another card and my list is alphebetized and chronological.
So I get a 1995 Topps card or something, I'm inserting it between #57 and
#58. As I said in my earlier post, that 1995 Topps card now becomes card #
58, and the next then I want Excel to re-number all of the cards below that.
I am not very technical savvy (sp?), but I'm hoping there is an easy way of
doing this. I hope this makes more sense......this is what I'm wanting to do. I have a spreadsheet of sports cards of a
particular player that I collect. I'm wanting to have a column of number,
1,2,3,4,5,..... etc so I'll know how many cards of that particular player I
have. Now here is my problem. Because my spreadsheet is alphabetized and
also is done by year, starting with 1989 when the player was drafted,
anytime I get a new card I insert a row into the spreadsheet to input the
card information. So what I want it to do when I insert a new row is
re-number all the columns. So in other words if I insert a row between #3
and #4. #4 should be card #5 now, and so on down the column. Can this be
done? Thanks!
 
R

RagDyer

Unstead of simply numbering the leftmost column (column A), you could enter
a formula which will produce a number depending on the number of the row
that the data resides in.

That way, when you insert a new row, *all* the others rows are renumbered by
XL, and since your item number is linked to the *actual* row number of the
sheet, your item numbers will updte automtically.

This formula,
=ROW()
entered in A1 will return a "one" (1).

BUT, if your data starts, on lets say row 3, enter this formula in A3,
=ROW()-2

This will return the number "one" (1).
Row number 3, minus the 2, equals one.

After you enter your adjusted (for row) formula in your first row of data,
select this cell again.
You will see a small black square in the lower right corner of the selected
cell.
This is called the "fill handle".
Hover the cursoe over the fill handle, until the cursor changes from a fat
whie cross, to a skinny black cross.
Now, click and drag down to copy in column A, as far as needed.

Now, when you insert a row, all your numbers will automatically update,
*except* for the row that you inserted.
That's because it doesn't yet have your formula in it.

Simply click in the row above the insertion, and drag down to copy as you
did before, to the row(s) that you just inserted, to bring this new row into
the numbering system.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Well I'm not technical savvy, so how in the world do I do this Ragdyer? I
haven't tried anything you guys have said because I don't understand what
you mean. ha ha, like I said, I don't understand all the formula stuff in
Excel anymore. I'm lost!
 
A

Andy Monson

RagDyer, you are THE MAN!!! Thank you so much. You have solved a problem that I have been trying to figure out for months. Thank you so much!
 
Top