Inserting a new row creates a blank in a cell that identifies row's alphabetical placement

[januerry]

Hello All:

I have a column that identifies where each row stands based on the alphabetical last name entry. When I add a new name to the list, the cell in that column is blank. Is there a way to have it automatically populate that cell with the next number in sequence and adjust all subsequent rows?

Further, what happens if I sort the data in some other order than alphabetical?

Call me puzzled!

Thanks for any assistance.

Jerry Scherer

[Richard Buttrey]

Hi Jerry,

I'm struggling to visualise exactly what you mean. You'll need to upload the workbook and indicate what name you add, where and what you expect to see elsewhere.

[januerry]

Thanks for your note. I am attaching a sample of what I am trying to accomplish. See if that helps.

[Richard Buttrey]

Hi,

Put this formula in D3 and copy down

Please Login or Register  to view this content.

Now add the following Double Click Event Macro

Please Login or Register  to view this content.

Whenever you double click a row it will insert a new row and add the formula for you.

The other option is to first add the formulae and then convert the range into a TABLE. Then whenever you manually insert a row the formula will be copied automatically.

[januerry]

Thank you very much. How do I tell Excel that this Macro exists in this workbook? Also, I still have concerns about what happens if I sort the data by something other than Last Name.

[Richard Buttrey]

Hi,

Right click the sheet tab name, Select View Code, in the left had drop down over on the right choose Worksheet, in the right hand drop down choose the BeforeDoubleClick procedure and add the code there.

However you sort the data the column with the formula will always contain incremental numbers.

[januerry]

Richard, you have been more than helpful. However, I still can't seem to get this right. The attached is a screen shot of what happens when I try to save the VB file. Am I doing something wrong? Also, note that the macro code does not appear.

[popipipo]

xlsx files cannot contain any macro
Save the file as .xlsm and not as xlsx

[januerry]

Thank you all for your contributions. Very much appreciated.