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

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.

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.

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.

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

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.

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.

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

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 ...

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.

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

You're welcome!

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1