I have an Excel list in one sheet with a form attached in a separate sheet -
one is a master list of quotes issued and the other is the form to fill out
to complete a quote. Currently a user has to get the next quote number in
the list manually and then enter into the quote form.

The quote takes the format of Q <next number <initials

What I need to achieve is for the user to open the form and be prompted to
give their initials. The form would then grab the next available number in
the list and apply those initials to that number - thus making the quote
number complete. The code would then write that number back to the Quote
Number field of the original list along with a few other details once the
rest of the form has gone about its business.

I can sort of achieve most of this, but the code is very long-winded and
clunky (based upon a macro, basically). I know I could achieve this in
Access however it is causing more problems than it is worth with regards to
linking fields and such - so I need a more efficient Excel code. Can anyone
help?

A formula should be able to do this:

say initials in column B and ID in A then define a named range/formula:

=TEXT(ROW();"#") & Sheet1!$B1 while standing in cell A1 of sheet1

now you can either fill this ID Down, or insert in on a new quote or
use it as a cell validation formula (just try it for a change you'll
see how it works). You can insert the formula itsself (will update ID
when initials change) or just it's value.

Hope this points in the right direction

DM unseen

Getting an error in the ROW() function...?

Also, initials and ID are the same thing

"DM Unseen" wrote:

A formula should be able to do this:

say initials in column B and ID in A then define a named range/formula:

=TEXT(ROW();"#") & Sheet1!$B1 while standing in cell A1 of sheet1

now you can either fill this ID Down, or insert in on a new quote or
use it as a cell validation formula (just try it for a change you'll
see how it works). You can insert the formula itsself (will update ID
when initials change) or just it's value.

Hope this points in the right direction

DM unseen

Sorry,

Im mean Quote ID = <next number <initials

Note that ROW() will get the row number of the current cell,(but maybe
you use other language?)
Please look this up in Excel help.


DM Unseen

I'm failing to see how ROW() applies here - going on what you've said I'm
using that as a formula in a cell (was that what you meant) and it's throwing
up an error because ROW does not accept the argument without further
conditions...?!

"DM Unseen" wrote:

Sorry,

Im mean Quote ID = <next number <initials

Note that ROW() will get the row number of the current cell,(but maybe
you use other language?)
Please look this up in Excel help.


DM Unseen