hard excel question

B

bp

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
 
B

Bob Phillips

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)
 
B

bp

sweet,
i will try it out

Bob Phillips said:
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)
Click to expand...
 
H

Harlan Grove

Bob Phillips wrote...
Maybe

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

in A2 and copy down
Click to expand...
....

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),"")
 
Top