# Formula to insert number in one column when an item is introduced in another column

1. ## Formula to insert number in one column when an item is introduced in another column

In the workbook attached, column C contains some numbering while D contains items being numbered. What would be the formula that would automatically introduce the next number in column C when the next item is introduced in column D?

Also, is there a way to to push down the total and the average as more items are added without inserting new rows?

Thank you.

Auto Numbering.xlsx  Register To Reply

2. ## Re: Formula to insert number in one column when an item is introduced in another column

For C

in C4

=C3+1

is there a way to to push down the total and the average as more items are added without inserting new rows?
n
No!

Logically , moving the total down is equivalent to adding rows.

The easiest alternative is the place the AVERAGE/TOTAL ABOVE the headings in row 2.  Register To Reply

3. ## Re: Formula to insert number in one column when an item is introduced in another column

Thank you john for the quick response. The formula I needed is one that would also delete the number when items in column D are also deleted.  Register To Reply

4. ## Re: Formula to insert number in one column when an item is introduced in another column

hi there. if you don't mind a slight change in the layout, convert this to a Table. click on C2 and press CTRL + T. press OK. paste this formula in C3:
=IF([@Item]<>"",ROW()-2,"")
copy down.

While selecting the Table, go to the Design Tab. Check on Total Row. that sums up column E. do a dropdown for D8 and choose Average. it shows an error because it is summing up column D (which are texts). change the formula to:
=SUBTOTAL(101,[Quantity])

to add rows, press TAB key in the last row of column E (minus the subtotal). right now in my eg file, it's E8.  Register To Reply

5. ## Re: Formula to insert number in one column when an item is introduced in another column

Try

in C4

=COUNTIF(\$D\$4:D4,"?*")

Copy down  Register To Reply

6. ## Re: Formula to insert number in one column when an item is introduced in another column

Thank you benishiryo.

John, your formula is close to what I want. However, deleting items in column D changes the number in that row under column C to the number in the previous row instead of not displaying any number at all.  Register To Reply

7. ## Re: Formula to insert number in one column when an item is introduced in another column

So you want to retain original numbers: perhaps you should be more specific in defining your requirements!

I assume you have an answer from benishiryo.  Register To Reply

8. ## Re: Formula to insert number in one column when an item is introduced in another column

Check the workbook below:

Cells D7:D9 are empty but 4 is displayed in cells C7:C9. I want a situation where if for example cells D7:D9 are empty, then no number should be displayed in cells C7:C9. The numbers should only be displayed if there is data in cells D7:D9.

Auto Numbering.xlsx  Register To Reply

9. ## Re: Formula to insert number in one column when an item is introduced in another column

Try this:

=IF(D3="","",COUNTIF(\$D\$3:D3,"?*"))

You ought to be familiar with basic IF statements that test whether or not a cell is blank by now ...   Register To Reply

10. ## Re: Formula to insert number in one column when an item is introduced in another column

Thank you AliGW. This is what I needed. Thanks to John and benishiryo too.  Register To Reply

11. ## Re: Formula to insert number in one column when an item is introduced in another column

You're welcome!   Register To Reply