Sequentially numbering specific cells

**dodgerlove** · 04-22-2010, 05:01 PM

Hello. I'm fairly new to excel and i'd appreciate someones help. I need help with sequentially numbering specific excel cells This link will provide you with the worksheet that i am currently working on http://www.mediafire.com/?twtmtydo4od -- we use this label every single day and if you look at next to " CTN: " on the middle right portion of the label, that is the # of the label. My question is, is there any macro or formula that i can use to sequentially number each label's "CTN " cell automatically? For now we are changing each number manually and as you can tell it takes a lot of time. If anyone can give me any kind of assistance i'd greatly appreciate it.

Thank you so much in advance.

EDIT: Sorry for linking a second party download link. I've attached the file through this post.

**darkyam** · 04-22-2010, 05:19 PM

People here generally don't download files from other sites. Please click on Go Advanced and then use the paper clip icon to attach your file. Thanks.

**dodgerlove** · 04-22-2010, 08:04 PM

bump bump bump

**darkyam** · 04-22-2010, 08:20 PM

When you edit a post, it doesn't alert those who have posted on it that it has been changed. We're only alerted when you add a new post. Also, three hours is not too long to wait for an answer. We're all volunteers, and most of us have jobs and families to attend to.

In Q28, use =MAX(IF(R$9:R27=$R$9,Q$9:Q27))+1, confirmed with Control+Shift+Enter (not just Enter). As a side note, I strongly recommend doing away with all merged cells. You can get the same effect by going to format cells, alignment tab, then choosing Center across selection under the Horizontal dropdown. Merging cells often causes problems with sorting, copy/pasting, and formulas.

**dodgerlove** · 04-22-2010, 08:46 PM

Darkyam, thank you for your response. The earlier file didn't really show what i needed and i re uploaded the file. In the revised uploaded file, next to the CTN cell it will all have the number 1. From the 14 CTN cells, i need the numbers to sequentially increase. The formula that you posted didn't do that for all the labels, except the 2nd. Pardon me if i'm new to this and i don't know how to use that or i'm missing something here. But could you further explain this to me? Thank you very much.

(sorry about my impatience. i was at work and i thought i'd be able to solve this by tonight.

)

**darkyam** · 04-22-2010, 09:30 PM

It works fine for me. You don't copy and paste the text of the formula in each successive cell. You copy and paste it once, and then copy the cell and paste it. The references in the cell will update. As long as the first formula was confirmed with CSE, copying the cell down where needed should work.

**shg** · 04-22-2010, 10:44 PM

I'll just jump in and say this, since it's a frequent topic among mods as to whether to broach the issue.

You spreadsheet organization is ... forgive me, awful.

A database has a header row, one record per row below, and a column for each field, redundant per record as appropriate, and NO MERGED CELLS.

A presentation sheet is extracted from the database by formula, filtering, or VBA. It can have pretty formatting, merged cells, and anything else you believe cosmetic. It should not be confused with the business (functional) end of the workbook.

I feel better, thanks.

**dodgerlove** · 04-23-2010, 11:19 AM

Haha. I know I know, ideally for organizing data this isn't the best approach or a good one at that. But these are for labels that we put on boxes we ship out from work. But Darkyam, i can't thank you enough for helping me with this. It does work and i was not using it correctly. Again, i can't thank you enough for this. Thank you.