Extending Row()

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Colin Hayes - 20 Dec 2005 22:49 GMT

Hi

I have the name of the month in cell A1 in each of 12 sheets.

From A5 onwards down the page I have the formula

=ROW()-4

which numbers each line. Ideally I would like the numbers to include at
the beginning the first three letters of each month. Each line would
then read Jan001 , Jan002 etc.

Can someone advise how to amend my

=ROW()-4

code to include these characters?

Any help appreciated. Thanks.

Colin

Reply to this Message

Trevor Shuttleworth - 20 Dec 2005 22:54 GMT

Try

="Jan" & TEXT(ROW()-4,"000")

Regards

Trevor

> Hi
>
[quoted text clipped - 17 lines]
>
> Colin

Reply to this Message

Colin Hayes - 20 Dec 2005 23:22 GMT

Hi Trevor

OK Thanks for that.

I've managed to work it out using your formula below and adding a code
to read the first three characters of the name of the month in A1.

=LEFT(A$1,3)&TEXT(ROW()-4,"000")

This gives me Jan001 etc all through the workbook and changes the first
three characters according to whatever is in A1. That worked out neatly.

Thanks.

Colin

>Try
>
[quoted text clipped - 25 lines]
>>
>> Colin

Reply to this Message

Elkar - 20 Dec 2005 23:10 GMT

Try this:

=CONCATENATE(LEFT($A$1,3),TEXT(ROW()-4,"000"))

HTH,
Elkar

> Hi
>
[quoted text clipped - 17 lines]
>
> Colin

Reply to this Message

Colin Hayes - 20 Dec 2005 23:41 GMT

Hi

OK Thanks - that's got it!

^_^

Colin

>Try this:
>
[quoted text clipped - 24 lines]
>>
>> Colin

Are you aware that we also buy CDs, Vinyl and DVDs? Send your lists of
unwanted items to Colin@chayes.demon.co.uk and we’ll quote you a price…

You can browse and buy direct from my full list of items at these addresses :

http://s1.amazon.co.uk/exec/varzea/ts/my-zshop/SPJK3X6KOJZR6/026-3393902-9
050050

or:

http://www.CDandVinyl.co.uk

or :

http://www.netsoundsmusic.com/chayes

or:

http://chayes.musicstack.com

To DOWNLOAD the full catalogue click here :

http://www.chayes.demon.co.uk/chayes_full_catalogue.exe

Best Wishes ,

Colin Hayes.

TEL / FAX : (UK) (0)208 804 9181

Reply to this Message