automated ref number generation

**Sal_m_78** · 10-07-2009, 04:27 PM

hi, what im trying to achieve is generating ref numbers automatically.

I have attached an example.

In Cell A if the user choose yes it gives a ref number. The trouble im having is that say in cell A5 a Yes is choosen at a later date, it will throw the numbers out of order and the ref numbers get mixed up, there any way of stopping this from happening or any function that prevents this from happening

A pointer in the right direction would be appreciated

Sal

**JP Romano** · 10-07-2009, 04:33 PM

Are you looking to do this with a formula or in a macro?

A little more info on what you're trying to accomplish would help too. There are a lot of really, really smart people here, and the more they know, the more likely you are to get your solution!

**Sal_m_78** · 10-07-2009, 04:42 PM

Apologies for the lack of description.

What i need to do this everytime a user says yes in the field a ref number is generated and this works fine if the user is always sure. But now and again the user will go back and change his mind from a no to yes which throws off the numbering. For example

Generate Change Request form Yes/No Index Number

Yes 1
No
Yes 2

after the user changes the no to yes, the index numbers change which mixes up the change request forms, so when we come 2 look for them they are never correct.

Generate Change Request form Yes/No Index Number

Yes 1
Yes 2
Yes 3

No to sure if this has clarified my issue.

Thank you

Sal

**JP Romano** · 10-07-2009, 04:47 PM

Thanks for the info. Is it necessary for the reference items to be consecutive? Will there be an expected number of lines in which the users can/will enter the Yes or No?

**JP Romano** · 10-07-2009, 04:50 PM

You might consider something like this:

= IF(A3="Yes",ROW(A3),"")

That will simply return the row number the Yes is on - it'll never change
... no math required!

**Sal_m_78** · 10-08-2009, 03:09 AM

Originally Posted by JP Romano

You might consider something like this:

= IF(A3="Yes",ROW(A3),"")

That will simply return the row number the Yes is on - it'll never change
... no math required!

Thanks for the solution.

Ok im going to be awkard now and say what if it was neccessary for the numbers to be consecutive. How would you go about doing that?

Thanks,

Sal

**Sal_m_78** · 10-07-2009, 05:09 PM

Originally Posted by JP Romano

Thanks for the info. Is it necessary for the reference items to be consecutive? Will there be an expected number of lines in which the users can/will enter the Yes or No?

No and No

This will go on for however long they intend to keep this system.

Thanks

Sal