COPY/PASTE/FORMULA GIVES ABSOLUTE REFERENCE
 

copy/paste special/formula doesn't copy the relative reference. It copies
the same formula in every cell in the column . I want c2, c3,c4,c5...
instead of c2 in every cell. Is this because it is in a function?

Also, can't I get it to be blank in the cell if there is no match? All I
can see is the formula in every cell. It would be nice if it was blank.

tia,


=IF(LEFT(C2,4)="ur01","XX.XX.251.188",IF(LEFT(C2,4 )="ur02","XX.XX.251.189",IF(LEFT(C2,4)="ur03","XX. XX.251.192",if(left(C2,4)="ur04","XX.XX.251.193","
"),""),""),"")

COPY/PASTE/FORMULA GIVES ABSOLUTE REFERENCE
 

First the formula is wrong: this is despite it being given to you earlier.

=IF(LEFT(C2,4)="ur01","XX.XX.251.188",IF(LEFT(C2,4 )="ur02","XX.XX.251.189",IF(LEFT(C2,4)="ur03","XX. XX.251.192",IF(LEFT(C2,4)="ur04","XX.XX.251.193"," "))))

Place this formula in the first cell, then simply drag the formula down -
click on cell and move cursor to bottom right of cell until cross appears:
hold down left mouse button and drag down column. The C2 references will
automatically update to C3 etc.

"Janis" wrote:

copy/paste special/formula doesn't copy the relative reference. It copies
the same formula in every cell in the column . I want c2, c3,c4,c5...
instead of c2 in every cell. Is this because it is in a function?

Also, can't I get it to be blank in the cell if there is no match? All I
can see is the formula in every cell. It would be nice if it was blank.

tia,


=IF(LEFT(C2,4)="ur01","XX.XX.251.188",IF(LEFT(C2,4 )="ur02","XX.XX.251.189",IF(LEFT(C2,4)="ur03","XX. XX.251.192",if(left(C2,4)="ur04","XX.XX.251.193","
"),""),""),"")

COPY/PASTE/FORMULA GIVES ABSOLUTE REFERENCE
 

If you can only see the formulas, it sounds like your cells are preformatted as
text (and that's the way text would be treated when you pasted).

Try changing the format of those cells to General (or anything but Text) and do
your copy|Paste.

But you'll want to fix your formula. It's still broken.

Check one of your previous threads for lots of alternatives.

Janis wrote:

copy/paste special/formula doesn't copy the relative reference. It copies
the same formula in every cell in the column . I want c2, c3,c4,c5...
instead of c2 in every cell. Is this because it is in a function?

Also, can't I get it to be blank in the cell if there is no match? All I
can see is the formula in every cell. It would be nice if it was blank.

tia,


=IF(LEFT(C2,4)="ur01","XX.XX.251.188",IF(LEFT(C2,4 )="ur02","XX.XX.251.189",IF(LEFT(C2,4)="ur03","XX. XX.251.192",if(left(C2,4)="ur04","XX.XX.251.193","
"),""),""),"")

--

Dave Peterson