Dynamic numbering

Naveen Srikakulam

Hi, I wanted to know if its possible to have the entry number as the Function + RAID item(R,A,I,D) + number. What I am looking for is for the R,A,I,D items to be named as per the functions- i.e. if its the 'first' R in Funciton1 , the result should be - Function 1. R.1 and if its the 'third' A in function 4, the result should be - Function 4. A.3. Please help! Thanks!

David Tutwiler

Ok, so I got it working, but I added a Unique Counts column to break the formulas up a bit. You could combine it, but the formula will get a little wild at that point.

The formula in the Unique Counts column is:

=COUNTIFS(RAID$1:RAID@row, RAID@row, Function$1:Function@row, Function@row)

Where you're counting the instances where the RAID value is equal to the current RAID value and the function value is equal to the current value up to that row (whichever row you're on). This should get you the unique value of how many times the letter has been used to this point.

Then in the Description column I am putting everything together by doing:

=Function1 + ". " + RAID1 + "." + [Unique Counts]@row

Where you're pulling the various elements together and adding the periods you had in your statement. If you wanted it to be all in one you could replace the formula in the Unique Counts Column where it says [Unique Counts]@row.

David Tutwiler

Just to clarify, in your example pictured your results would be:

Function 1. R.1

Function 2. A.1

Function 1. R.2

Function 2. R.1

Correct?

Naveen Srikakulam

Hi David. Yes you are correct!

David Tutwiler

Would it be possible to use a custom sort to sort by Function Name, then by RAID letter? If so that makes the formula much easier.

Naveen Srikakulam

Hi David. The Entry no is linked to some other sheets and so I cant sort it. I was hoping to have dynamic numbering so that it can be pulled to these sheets immediately.

David Tutwiler

Ok, so I got it working, but I added a Unique Counts column to break the formulas up a bit. You could combine it, but the formula will get a little wild at that point.

The formula in the Unique Counts column is:

=COUNTIFS(RAID$1:RAID@row, RAID@row, Function$1:Function@row, Function@row)

Where you're counting the instances where the RAID value is equal to the current RAID value and the function value is equal to the current value up to that row (whichever row you're on). This should get you the unique value of how many times the letter has been used to this point.

Then in the Description column I am putting everything together by doing:

=Function1 + ". " + RAID1 + "." + [Unique Counts]@row

Where you're pulling the various elements together and adding the periods you had in your statement. If you wanted it to be all in one you could replace the formula in the Unique Counts Column where it says [Unique Counts]@row.

Naveen Srikakulam

Awesome, David! It worked like a charm. Thanks for solving my query! Cheers! :)

David Tutwiler

No problem. Glad it's working.