Changing relative to absolute for a column
 

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?

Changing relative to absolute for a column
 

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

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

I hope I have explained this properly


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=542704

Changing relative to absolute for a column
 

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


"John K" wrote:

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?

Changing relative to absolute for a column
 

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" wrote in message
...
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?

Changing relative to absolute for a column
 

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.

"Bob Phillips" wrote:

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" wrote in message
...
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?

Changing relative to absolute for a column
 

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?


"John K" wrote:

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.

"Bob Phillips" wrote:

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" wrote in message
...
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?

Changing relative to absolute for a column
 

John K Wrote:
It doesn't really matter now. [/color]
Well, Thanks for replying back


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=542704