This will probably sound silly, and I'm sure I once knew how to do it, but if so, I've forgotten. I have a workbook with several sheets. And I have a formula in cell D7 that works the way I want it to. However, I need the same formula in cells D8 through DS8. That part I can get by simply clicking the cell with the formula, clicking COPY, then selecting cells D7 through DS7 and clicking PASTE. That's because I have absolute references in the formula, so that it stays the same when I paste it into multiple cells in this fashion. The formul is as follows:
@IF($E$2="","",@IF($E$2="N"#AND#($I$3="Y"#OR#$M$3="Y"),"MAX",""))
Now for the problem. Once the above procedure is done, I now have the formula in cells D7 through DS7. However, now I want to copy that range of formulas to sheets G through BC. And if I select the cells D7 thrugh DS7, and click COPY, then move to sheet G, select cell D7, and choose PASTE, it pastes the formula into all the cells D7 through DS7 properly, HOWEVER, it precedes the cell references in the formula with the sheet reference of the sheet I copied from. That is, the original formula becomes:
@IF(F:$E$2="","",@IF(F:$E$2="N"#AND#(F:$I$3="Y"#OR#F:$M$3="Y"),"MAX",""))
Now, of course, I can go through each page, and paste each range of cells individually. But is there a way to select the range I want to paste to on all the sheets, then paste in one action, WITHOUT having 1-2-3 insert the reference to the original sheet into the formula?
Return to top
Copying Absolute Formulas (Raymond P McInt... 17.Oct.10)
. . 
Print this page
