Populating a cell with numbers without numbers being formula-based??
This is something I've run across again and again that I hope to now get a
solution for. Most of the time, I use =ROW-1, or a variation thereof, to get column A to show numbers 1 to whatever, depending on the # of rows in a print area. However, every once in a while numbers _must_ still display sequentially, but must be "real" numbers. So instead of =ROW-1 showing in a cell, one would see an actual #, 1, 2, 3, 4 ... etc. I've always had to type these in manually. Today I had to type in up to row 100! My question is if it's possible to programmatically dump the numbers in in a selected number of cells downwards in column A?? i.e., if I were to select from A2 to A51, for example, could the macro start filling in the cells numerically - A2 would then have a 1 in it, A3 would have 2, A4 would have 3, etc., etc.? This would be a god-send, if it were possible to do. I just got to thinking, what if the case were of running out of rows in a spreadsheet? Let's say a sheet that reached row #A51 which had number 50 in it. I'd have to add rows after that point. If I added 50 more rows and then selected A52 to A101, would there be a way to have the macro then populate the cells from 51 to 100? To do both, starting from 1 like the above case, or starting from 51 like this second case, I'm guessing it would be a matter of prompting the user for the starting number to begin the numbering (?). Just a thought. I don't know if this can be done, but it would save so much grief and time and would save some poor fingers! <g Thanks! |
Populating a cell with numbers without numbers being formula-based??
One method of populating a column with numbers is to enter 1 in top cell.
Right-click on fill handle of the this cell and drag down 100 rows. Release the button and select "Fill Series" from the menu. Another method is to enter 1 in the top cell, select the namebox and type in A1:A100 and <ENTER (A1:A100 to be adjusted to your range and column) Now, EditFillSeriesLinearOK If you need numbers later past 100, just select 99 and 100 and drag/copy down. Gord Dibben Excel MVP On Tue, 13 Jul 2004 16:43:30 -0400, "StargateFanFromWork" wrote: This is something I've run across again and again that I hope to now get a solution for. Most of the time, I use =ROW-1, or a variation thereof, to get column A to show numbers 1 to whatever, depending on the # of rows in a print area. However, every once in a while numbers _must_ still display sequentially, but must be "real" numbers. So instead of =ROW-1 showing in a cell, one would see an actual #, 1, 2, 3, 4 ... etc. I've always had to type these in manually. Today I had to type in up to row 100! My question is if it's possible to programmatically dump the numbers in in a selected number of cells downwards in column A?? i.e., if I were to select from A2 to A51, for example, could the macro start filling in the cells numerically - A2 would then have a 1 in it, A3 would have 2, A4 would have 3, etc., etc.? This would be a god-send, if it were possible to do. I just got to thinking, what if the case were of running out of rows in a spreadsheet? Let's say a sheet that reached row #A51 which had number 50 in it. I'd have to add rows after that point. If I added 50 more rows and then selected A52 to A101, would there be a way to have the macro then populate the cells from 51 to 100? To do both, starting from 1 like the above case, or starting from 51 like this second case, I'm guessing it would be a matter of prompting the user for the starting number to begin the numbering (?). Just a thought. I don't know if this can be done, but it would save so much grief and time and would save some poor fingers! <g Thanks! |
Populating a cell with numbers without numbers being formula-based??
Hi,
Assuming your list started from E26 try: =ROW()-ROW($E$26)+1 and copy it down. HTH Peter Beach "StargateFanFromWork" wrote in message ... This is something I've run across again and again that I hope to now get a solution for. Most of the time, I use =ROW-1, or a variation thereof, to get column A to show numbers 1 to whatever, depending on the # of rows in a print area. However, every once in a while numbers _must_ still display sequentially, but must be "real" numbers. So instead of =ROW-1 showing in a cell, one would see an actual #, 1, 2, 3, 4 ... etc. I've always had to type these in manually. Today I had to type in up to row 100! My question is if it's possible to programmatically dump the numbers in in a selected number of cells downwards in column A?? i.e., if I were to select from A2 to A51, for example, could the macro start filling in the cells numerically - A2 would then have a 1 in it, A3 would have 2, A4 would have 3, etc., etc.? This would be a god-send, if it were possible to do. I just got to thinking, what if the case were of running out of rows in a spreadsheet? Let's say a sheet that reached row #A51 which had number 50 in it. I'd have to add rows after that point. If I added 50 more rows and then selected A52 to A101, would there be a way to have the macro then populate the cells from 51 to 100? To do both, starting from 1 like the above case, or starting from 51 like this second case, I'm guessing it would be a matter of prompting the user for the starting number to begin the numbering (?). Just a thought. I don't know if this can be done, but it would save so much grief and time and would save some poor fingers! <g Thanks! |
Populating a cell with numbers without numbers being formula-based??
On Tue, 13 Jul 2004 14:38:10 -0700, Gord Dibben <gorddibbATshawDOTca
wrote: One method of populating a column with numbers is to enter 1 in top cell. Right-click on fill handle of the this cell and drag down 100 rows. Release the button and select "Fill Series" from the menu. Oh, god, to think of all these years when I've been typing all the numbers in manually! <lol (Told yas I was a newbie even if a power user! D'uh! <g) Unbelievably easy! This worked just fine. The method below would be best if I knew ahead of time what the beginning and end cells would be. I see a use for both methods. Thank you so much for this! <g Another method is to enter 1 in the top cell, select the namebox and type in A1:A100 and <ENTER (A1:A100 to be adjusted to your range and column) Now, EditFillSeriesLinearOK If you need numbers later past 100, just select 99 and 100 and drag/copy down. Gord Dibben Excel MVP On Tue, 13 Jul 2004 16:43:30 -0400, "StargateFanFromWork" wrote: This is something I've run across again and again that I hope to now get a solution for. Most of the time, I use =ROW-1, or a variation thereof, to get column A to show numbers 1 to whatever, depending on the # of rows in a print area. However, every once in a while numbers _must_ still display sequentially, but must be "real" numbers. So instead of =ROW-1 showing in a cell, one would see an actual #, 1, 2, 3, 4 ... etc. I've always had to type these in manually. Today I had to type in up to row 100! My question is if it's possible to programmatically dump the numbers in in a selected number of cells downwards in column A?? i.e., if I were to select from A2 to A51, for example, could the macro start filling in the cells numerically - A2 would then have a 1 in it, A3 would have 2, A4 would have 3, etc., etc.? This would be a god-send, if it were possible to do. I just got to thinking, what if the case were of running out of rows in a spreadsheet? Let's say a sheet that reached row #A51 which had number 50 in it. I'd have to add rows after that point. If I added 50 more rows and then selected A52 to A101, would there be a way to have the macro then populate the cells from 51 to 100? To do both, starting from 1 like the above case, or starting from 51 like this second case, I'm guessing it would be a matter of prompting the user for the starting number to begin the numbering (?). Just a thought. I don't know if this can be done, but it would save so much grief and time and would save some poor fingers! <g Thanks! |
Populating a cell with numbers without numbers being formula-based??
On Wed, 14 Jul 2004 10:22:21 +1200, "Peter Beach"
wrote: Hi, Assuming your list started from E26 try: =ROW()-ROW($E$26)+1 I tried this and unless I'm missing something there seems to be no benefit that I could see over =ROW()-1 or variation thereof. If I re-sort the info in the second column, the above adjusts the numbering just like =ROW()-1. See, most of the time, the sheets I'm making are sorted by date. So any new record must go in sequence and the numbering in column A needs to accommodate the new entry. So if one day a particular piece of correspondence went with record #15, if we find a previous letter and incorporate it into the index sheet, the above might then belong to #16. =ROW()-1 works perfectly in this type of situation. But there are couple of index sheets where the numbers need to be inflexible. For those binders where data is added and deleted constantly, it turned out to be best to have inflexible row #'s, i.e., numerically present rather than present through a formula. We sort by the data and the numbers are all out of whack, since unlike =ROW()-1 no re-shifting occurs. But one finds the correspondence with no problem as the index sheet is sorted by date not row #. When we didn't do this, every time we deleted an entry all the records would shift re the row # and that would have meant re-shuffling all the documents within the binder to correspond to the index tab. Much easier to do a sort to find an empty record to put the new entry in and then to re-sort by date. Means that when you physically are looking through the binder all the documentation is out of chronological order - but it's the index sheet that keeps everything organized. Thanks! and copy it down. HTH Peter Beach "StargateFanFromWork" wrote in message ... This is something I've run across again and again that I hope to now get a solution for. Most of the time, I use =ROW-1, or a variation thereof, to get column A to show numbers 1 to whatever, depending on the # of rows in a print area. However, every once in a while numbers _must_ still display sequentially, but must be "real" numbers. So instead of =ROW-1 showing in a cell, one would see an actual #, 1, 2, 3, 4 ... etc. I've always had to type these in manually. Today I had to type in up to row 100! My question is if it's possible to programmatically dump the numbers in in a selected number of cells downwards in column A?? i.e., if I were to select from A2 to A51, for example, could the macro start filling in the cells numerically - A2 would then have a 1 in it, A3 would have 2, A4 would have 3, etc., etc.? This would be a god-send, if it were possible to do. I just got to thinking, what if the case were of running out of rows in a spreadsheet? Let's say a sheet that reached row #A51 which had number 50 in it. I'd have to add rows after that point. If I added 50 more rows and then selected A52 to A101, would there be a way to have the macro then populate the cells from 51 to 100? To do both, starting from 1 like the above case, or starting from 51 like this second case, I'm guessing it would be a matter of prompting the user for the starting number to begin the numbering (?). Just a thought. I don't know if this can be done, but it would save so much grief and time and would save some poor fingers! <g Thanks! |
All times are GMT +1. The time now is 07:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com