hard excel question

Hello,
I am trying to do something kinda complicated. I have 2 columns. the first
I would like to do a numbering. like F1.1,F1.2,F1.3, etc....
But I would only like a number if the second column has the word
"Automated". The column can also have the word "Manual" which I want the
first column to have nothing in it.

It would be pretty easy to insert a value in the first column, if there is a
specific value in the second. however I would like to do this numbering
thing.

can anyone help?
thanks

Maybe

=IF(B2="Automated","F"&MAX(MID(A1:A1,2,99))+0.1,"")

in A2 and copy down

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"bp" <[email protected]> wrote in message
news:e$KU%[email protected]...
> Hello,
> I am trying to do something kinda complicated. I have 2 columns. the
first
> I would like to do a numbering. like F1.1,F1.2,F1.3, etc....
> But I would only like a number if the second column has the word
> "Automated". The column can also have the word "Manual" which I want the
> first column to have nothing in it.
>
> It would be pretty easy to insert a value in the first column, if there is
a
> specific value in the second. however I would like to do this numbering
> thing.
>
> can anyone help?
> thanks
>
>

sweet,
i will try it out

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Maybe
>
> =IF(B2="Automated","F"&MAX(MID(A1:A1,2,99))+0.1,"")
>
> in A2 and copy down
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "bp" <[email protected]> wrote in message
> news:e$KU%[email protected]...
>> Hello,
>> I am trying to do something kinda complicated. I have 2 columns. the
> first
>> I would like to do a numbering. like F1.1,F1.2,F1.3, etc....
>> But I would only like a number if the second column has the word
>> "Automated". The column can also have the word "Manual" which I want
>> the
>> first column to have nothing in it.
>>
>> It would be pretty easy to insert a value in the first column, if there
>> is
> a
>> specific value in the second. however I would like to do this numbering
>> thing.
>>
>> can anyone help?
>> thanks
>>
>>
>
>

Bob Phillips wrote...
>Maybe
>
>=IF(B2="Automated","F"&MAX(MID(A1:A1,2,99))+0.1,"")
>
>in A2 and copy down
....

First, I'd guess you meant the A1:A1 to be A$1:A1; otherwise, just use
A1. Next, if B1 weren't "Automated" and A1 weren't "F1.1", your formula
would return "F0.1" the first time "Automated" appears in col B.
Further, MID returns strings, so MAX(MID(..)) will return zero. Yes,
the MAX call in MAX(MID(SingleCellRef,2,99)) treats the return value
from MID as numeric, but change SingleCellRef to a multile cell
reference, and MAX no longer converts text to numbers.

I believe you have to use something like

A2 [array formula]:
=IF(B2="Automated","F"&MAX(("0"&MID(A$1:A1,2,8))+0.1,1.1),"")