Can excel generate reference numbers with text?

  oo7juk 23:38 19 Sep 2007
Locked

Hi,

I'm trying to generate a reference number within excel, but can't seem to find anything yet.

I have tried =row and =text(row), but they are not doing what I want.

Basically when I enter data e.g. John in A1 I want cell B1 to populate with a ref number. The ref number I would like to use is WM/RA/01 and 02,03 etc. Can it be done? FYI I'm using excel 2003

Many thanks

  johnnyrocker 23:44 19 Sep 2007

stick around and vog will appear and set you on the right track


johnny.

  Simsy 23:50 19 Sep 2007

you have the information elsewhere in the workbook, in the form of a table, you can do this using a "lookup"

Check out "vlookup" and "hlookup" in the help files.

(You haven't explained how you get "WM/RA/01", so I'm assuming this is something predetermined?)

Does this help,

Regards,

Simsy

  oo7juk 00:10 20 Sep 2007

Simsy,

Yes I'm going to use vlookup, but was just wondering if excel can automatically generate number/text cells.

  Simsy 09:39 20 Sep 2007

using a macro, but what I think you need is beyond my skills.

I suspect getting a "next" number is fairly easy, but it may become more complex if you started deleting entries.

In the absence of Vog, who is excellent at this kind of thing, you could try at click here where they also hae excellent Excel folk.

You will probably need to explain the situation a little more fully. What is the orig of WA/RA etc.

Good luck,

Regards,

Simsy

  Picklefactory 11:41 20 Sep 2007

Do you just want to create a reference number as a one off allocation type thing? Will your list of names be entirely unique or do you want it to recognise duplicates? As Simsy says, really need a little more info. If you just want to type a unique name in a cell and have Excel generate a new sequential reference number, that is easy. But sounds too easy.

  daba 12:00 20 Sep 2007

if you want the cell to contain the row number of its row, just put =ROW( then click on the square at the left of the row, then hit enter.

wherever you copy that formula to, the cell will show the current row number

  IntoPCs 12:22 20 Sep 2007

you could try this
=CONCATENATE("WM/RA","/",(ROW(1:1)))
and replicate down the column or turn into an IF function so it only works when a particular cell is populated?

  IntoPCs 12:23 20 Sep 2007

made that complicated with the "/" ?
It could read
=CONCATENATE("WM/RA/,(ROW(1:1)))

  IntoPCs 12:26 20 Sep 2007

=IF(ISBLANK(A1),,CONCATENATE("WM/RA/,(ROW(1:1))))

  IntoPCs 12:28 20 Sep 2007

That's more like it

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone