auto numbering text

[Lee]

I have a cell (D6) that has text AV1 in it. Is it possible to have cell A20
check to see if B20 has a number greater than 0 in it, if it does return
AV1-1. If there isn't return a blank or "-".
At the moment A20 has the following formula in it:
=IF('A-V Input'!B20>0,"AV1-1","")
I need to copy this 20 times and the change "AV1-1" manually using the above
formula.
There are other parts but I want to try and solve this first

Thanks,

[Excel_Geek]

Not sure i completely get what you're after, but you can do this:

=IF('A-V Input'!B20>0,D6&"-1","")

to reference teh value "AV1" in cell D6 and add to it the text "-1".

Then if E6 and on down have AV2, AV3, etc., copying this formula down will reference them, and create "AV2-1", "AV3-1", etc.

Is this what you're after?

[Max]

One guess ..

Put in A20: =IF('A-V Input'!B20>0,INDIRECT($D$6)-1,"")
where D6 contains the text: AV1

Adapt to suit
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Lee" <[email protected]> wrote in message
news:[email protected]...
> I have a cell (D6) that has text AV1 in it. Is it possible to have cell
A20
> check to see if B20 has a number greater than 0 in it, if it does return
> AV1-1. If there isn't return a blank or "-".
> At the moment A20 has the following formula in it:
> =IF('A-V Input'!B20>0,"AV1-1","")
> I need to copy this 20 times and the change "AV1-1" manually using the
above
> formula.
> There are other parts but I want to try and solve this first
>
> Thanks,
>
>
>

[Lee]

Well you got part of it right. I need the AV1- to stay constent and the -
number to change 20 times.
example.
AV1-1
AV1-2
AV1-3 etc.

What i'm using for to calculate circuit voltage by device. So, AV1 is the
circuit and -1 is the device. There will be multiple circuits, so if I could
get the first one working I can use it to figure AV2-, AV3, etc.
I hope I cleared up what wanted to do or did I make it worse.


"Excel_Geek" wrote:

>
> Not sure i completely get what you're after, but you can do this:
>
> =IF('A-V Input'!B20>0,D6&"-1","")
>
> to reference teh value "AV1" in cell D6 and add to it the text "-1".
>
> Then if E6 and on down have AV2, AV3, etc., copying this formula down
> will reference them, and create "AV2-1", "AV3-1", etc.
>
> Is this what you're after?
>
>
> --
> Excel_Geek
>
>
> ------------------------------------------------------------------------
> Excel_Geek's Profile: http://www.excelforum.com/member.php...o&userid=26423
> View this thread: http://www.excelforum.com/showthread...hreadid=399602
>
>

[Max]

"Lee" wrote:
> .. I need the AV1- to stay constent and the -
> number to change 20 times.
> example.
> AV1-1
> AV1-2
> AV1-3 etc.

Still guessing here ..

Put in D6: AV (i.e. w/o the "1")

Then try in the starting cell, and copy down:
=IF('A-V Input'!B20>0,$D$6&ROWS($A$1:A1)&"-1","")

The incrementer part: ROWS($A$1:A1)
should serve the purpose here as you copy down
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Max]

Correstions, sorry, should be:

Put in D6: AV1

Then try in the starting cell, and copy down:
=IF('A-V Input'!B20>0,$D$6&"-"&ROWS($A$1:A1),"")

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Lee]

perfect

thanks alot

"Max" wrote:

> Correstions, sorry, should be:
>
> Put in D6: AV1
>
> Then try in the starting cell, and copy down:
> =IF('A-V Input'!B20>0,$D$6&"-"&ROWS($A$1:A1),"")
>
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>
>

[Max]

Glad to hear that !
You're welcome ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Lee" <[email protected]> wrote in message
news:[email protected]...
> perfect
>
> thanks alot