software

I have column A amp; B. In column C I have =iff(a1=b1,quot;Yquot;,quot;Nquot;). Now I drag
that down column c to the bottom of the list. I might have
=iff(a150=b150,quot;Yquot;,quot;Nquot;). 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?


=IF($A1=$B1,quot;Yquot;,quot;Nquot;)

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: www.excelforum.com/member.php...oamp;userid=31708
View this thread: www.excelforum.com/showthread...hreadid=542704How 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,
Elkarquot;John Kquot; wrote:

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

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)

quot;John Kquot; gt; wrote in message
...
gt; I have column A amp; B. In column C I have =iff(a1=b1,quot;Yquot;,quot;Nquot;). Now I drag
gt; that down column c to the bottom of the list. I might have
gt; =iff(a150=b150,quot;Yquot;,quot;Nquot;). Now I want to change a(row number) to a$(row
gt; number) and b(row number) to b$(row number) for the whole column. I know
I
gt; can go cell by cell and click on the function at the top and hit f4, but
if I
gt; got hundres of rows that's a lot. And I can't make the rows absolute
before
gt; draging down, that would keep it the same row. Is there a quick and easy
way
gt; to do this?
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.

quot;Bob Phillipsquot; wrote:

gt; Sub ConvertThem()
gt; Dim cell As Range
gt; For Each cell In Selection
gt; With cell
gt; .Formula = Application.ConvertFormula(.Formula, xlA1, xlA1,
gt; xlAbsRowRelColumn)
gt; End With
gt; Next cell
gt; End Sub
gt;
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;John Kquot; gt; wrote in message
gt; ...
gt; gt; I have column A amp; B. In column C I have =iff(a1=b1,quot;Yquot;,quot;Nquot;). Now I drag
gt; gt; that down column c to the bottom of the list. I might have
gt; gt; =iff(a150=b150,quot;Yquot;,quot;Nquot;). Now I want to change a(row number) to a$(row
gt; gt; number) and b(row number) to b$(row number) for the whole column. I know
gt; I
gt; gt; can go cell by cell and click on the function at the top and hit f4, but
gt; if I
gt; gt; got hundres of rows that's a lot. And I can't make the rows absolute
gt; before
gt; gt; draging down, that would keep it the same row. Is there a quick and easy
gt; way
gt; gt; to do this?
gt;
gt;
gt;

What about using the OFFSET function?

In C1 enter:
=IF(OFFSET(C1,0,-2)=OFFSET(C1,0,-1),quot;Yquot;,quot;Nquot;)

Does that accomplish what you want?quot;John Kquot; wrote:

gt; It doesn't really matter now. I tested it with the rows absolute and still
gt; if I deleted a cell in the A column and shifted everything up the row numbers
gt; moved up also. That's what I didn't want to change. What I wanted was the
gt; rows in the formular to stay a1,a2,... b1,b2,... and not change even if I
gt; deleted or added a cell in either the a or b column. But they still did even
gt; with $A$1 $B$1 and so on. I guess if I delete or add a row I'll just drag
gt; the formular down from a row above where I made the change.
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Sub ConvertThem()
gt; gt; Dim cell As Range
gt; gt; For Each cell In Selection
gt; gt; With cell
gt; gt; .Formula = Application.ConvertFormula(.Formula, xlA1, xlA1,
gt; gt; xlAbsRowRelColumn)
gt; gt; End With
gt; gt; Next cell
gt; gt; End Sub
gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from the email address if mailing direct)
gt; gt;
gt; gt; quot;John Kquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I have column A amp; B. In column C I have =iff(a1=b1,quot;Yquot;,quot;Nquot;). Now I drag
gt; gt; gt; that down column c to the bottom of the list. I might have
gt; gt; gt; =iff(a150=b150,quot;Yquot;,quot;Nquot;). Now I want to change a(row number) to a$(row
gt; gt; gt; number) and b(row number) to b$(row number) for the whole column. I know
gt; gt; I
gt; gt; gt; can go cell by cell and click on the function at the top and hit f4, but
gt; gt; if I
gt; gt; gt; got hundres of rows that's a lot. And I can't make the rows absolute
gt; gt; before
gt; gt; gt; draging down, that would keep it the same row. Is there a quick and easy
gt; gt; way
gt; gt; gt; to do this?
gt; gt;
gt; gt;
gt; gt;


John K Wrote:
gt; It doesn't really matter now. gt;[/color]
Well, Thanks for replying back--
davesexcel------------------------------------------------------------------------
davesexcel's Profile: www.excelforum.com/member.php...oamp;userid=31708
View this thread: www.excelforum.com/showthread...hreadid=542704