Changing relative to absolute for a column

[John K]

I have column A & B. In column C I have =iff(a1=b1,"Y","N"). Now I drag
that down column c to the bottom of the list. I might have
=iff(a150=b150,"Y","N"). Now I want to change a(row number) to a$(row
number) and b(row number) to b$(row number) for the whole column. I know I
can go cell by cell and click on the function at the top and hit f4, but if I
got hundres of rows that's a lot. And I can't make the rows absolute before
draging down, that would keep it the same row. Is there a quick and easy way
to do this?

 

[davesexcel]

=IF($A1=$B1,"Y","N")

Here's one way,
Highlight the entire range that you want the function and enter th
above function
then instead of pressing enter, press Ctrl enter
the formulas will now be entered into the entire range you hav
highlited and will have coresponded to the proper rows

I hope I have explained this properl

 

[Elkar]

How about doing a Find/Replace? Since A and B are only used once each in
your formulas, find A and replace with $A. Then find B and replace with $B.

HTH,
Elkar

 

[Bob Phillips]

Sub ConvertThem()
Dim cell As Range
For Each cell In Selection
With cell
.Formula = Application.ConvertFormula(.Formula, xlA1, xlA1,
xlAbsRowRelColumn)
End With
Next cell
End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[John K]

It doesn't really matter now. I tested it with the rows absolute and still
if I deleted a cell in the A column and shifted everything up the row numbers
moved up also. That's what I didn't want to change. What I wanted was the
rows in the formular to stay a1,a2,... b1,b2,... and not change even if I
deleted or added a cell in either the a or b column. But they still did even
with $A$1 $B$1 and so on. I guess if I delete or add a row I'll just drag
the formular down from a row above where I made the change.

 

[Elkar]

What about using the OFFSET function?

In C1 enter:
=IF(OFFSET(C1,0,-2)=OFFSET(C1,0,-1),"Y","N")

Does that accomplish what you want?

 

[davesexcel]

It doesn't really matter now. >

[/QUOTE]
Well, Thanks for replying back