Add text to cell formulas?
 

Hello. I have a tape label spreadsheet where the cells are laid out as:

CS2_A001
CS2_A001
CS2_A002
CS2_A002
CS2_A003
CS2_A003
etc.. etc....


So basically each cell is doubled. I'd like to setup a template where if I
manually type in the first cell (or assign an arbitrary number such as 020),
all the other cells will automatically change. How do I do this?

I tried doing "=A1+1" but I just get the value error :(. The formula doesn't
need to automatically double (in fact it shouldn't for customization
reasons). Just a formula where it would do something like: 'TEXT + 1', so
'CS2_A' would be the standard text, three number placeholders standard (000).

Add text to cell formulas?
 

One way:

=LEFT(A1,LEN(A1)-3) & TEXT(RIGHT(A1,3)+1,"000")

In article ,
phillr wrote:

Hello. I have a tape label spreadsheet where the cells are laid out as:

CS2_A001
CS2_A001
CS2_A002
CS2_A002
CS2_A003
CS2_A003
etc.. etc....


So basically each cell is doubled. I'd like to setup a template where if I
manually type in the first cell (or assign an arbitrary number such as 020),
all the other cells will automatically change. How do I do this?

I tried doing "=A1+1" but I just get the value error :(. The formula doesn't
need to automatically double (in fact it shouldn't for customization
reasons). Just a formula where it would do something like: 'TEXT + 1', so
'CS2_A' would be the standard text, three number placeholders standard (000).

Add text to cell formulas?
 

In A2 use

=A1+1

then format all cells as "CS2_"000

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"phillr" wrote in message
...
Hello. I have a tape label spreadsheet where the cells are laid out as:

CS2_A001
CS2_A001
CS2_A002
CS2_A002
CS2_A003
CS2_A003
etc.. etc....


So basically each cell is doubled. I'd like to setup a template where if I
manually type in the first cell (or assign an arbitrary number such as
020),
all the other cells will automatically change. How do I do this?

I tried doing "=A1+1" but I just get the value error :(. The formula
doesn't
need to automatically double (in fact it shouldn't for customization
reasons). Just a formula where it would do something like: 'TEXT + 1', so
'CS2_A' would be the standard text, three number placeholders standard
(000).

Add text to cell formulas?
 

Hi, Bob and JE:

The OP's list showed 2 copies of each number. If that's in fact what he wants,
I would use this approach:

in A2: =A1,
in A3: =A1+1

Copy the formula in A3 down. The result is that A2 is the 2nd copy of A1, and
from there on, the cell increments the value from 2 rows above.

Similarly, JE's formulas would be

in A2: =A1
in A3: =LEFT(A1,LEN(A1)-3) & TEXT(RIGHT(A1,3)+1,"000")

Again, copy A3 down (or select A2 and A3 and drag down with the fill handle).


On Tue, 30 Oct 2007 16:26:52 -0000, "Bob Phillips"
wrote:

In A2 use

=A1+1

then format all cells as "CS2_"000

Add text to cell formulas?
 

In article ,
Myrna Larson wrote:

The OP's list showed 2 copies of each number. If that's in fact what he wants,
I would use this approach:

From the OP:

The formula doesn't need to automatically double
(in fact it shouldn't for customization reasons).

Add text to cell formulas?
 

I missed that. I wonder why he showed that as his example if it isn't what he
wants :(

On Tue, 30 Oct 2007 13:36:12 -0600, JE McGimpsey wrote:

In article ,
Myrna Larson wrote:

The OP's list showed 2 copies of each number. If that's in fact what he
wants,
I would use this approach:

From the OP:

The formula doesn't need to automatically double
(in fact it shouldn't for customization reasons).

Add text to cell formulas?
 

Yes it's odd isn't it. I wasn't sure exactly what he meant (it was ambiguous
to say the least), but I interpreted as JE did.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

BTW Good to see you treading the boards again.


"Myrna Larson" wrote in message
...
I missed that. I wonder why he showed that as his example if it isn't what
he
wants :(

On Tue, 30 Oct 2007 13:36:12 -0600, JE McGimpsey
wrote:

In article ,
Myrna Larson wrote:

The OP's list showed 2 copies of each number. If that's in fact what he
wants,
I would use this approach:

From the OP:

The formula doesn't need to automatically double
(in fact it shouldn't for customization reasons).