How to update copied cells to relative and absolute references

%3CLINGO-SUB%20id%3D%22lingo-sub-2304103%22%20slang%3D%22en-US%22%3EHow%20to%20update%20copied%20cells%20to%20relative%20and%20absolute%20references%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2304103%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3EI'm%20running%3CBR%20%2F%3EWindows%2010%20Home%20OS%2064-bit%20Operating%20System%2C%20x64-based%20processor%3CBR%20%2F%3EMicrosoft%20365%2C%20ver.%202103%2C%20Build%2013901.20400%20Click-to-run%2064-bit%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20with%20which%20I%20track%20publicly-traded%20companies'%20earnings%20results.%3C%2FP%3E%3CP%3EOne%20of%20the%20data%20points%20I%20track%20is%20what%20percentage%20of%20total%20revenue%20various%20measures%20equal.%3C%2FP%3E%3CP%3EThe%20Total%20Revenue%20section%20is%20in%20a%20specific%20cell-range%20and%20I%20refer%20back%20to%20it%20time%20and%20again.%3C%2FP%3E%3CP%3EThe%20other%20measures%20are%20obviously%20in%20different%20cell-ranges%3B%20but%20the%20denominators%20are%20always%20D11%2C%20E11%2C%20F11%2C%20and%20so%20on.%3C%2FP%3E%3CP%3ETHE%20PROBLEM%20I'M%20TRYING%20TO%20SOLVE%20is%20how%20to%20copy%20the%20equations%20from%20one%20section%20to%20the%20next%2C%20and%20letting%20the%20numerators%20update%20as%20relative%20references%2C%20but%20locking%20the%20denominators%20down%20so%20they%20always%20refer%20back%20to%20D11%2C%20E11%2C%20F11%2C%20etc.%20ALL%20AT%20ONCE.%3C%2FP%3E%3CP%3EI've%20tried%20copying%20the%20equations%20as%20a%20block%20with%20relative%20references%2C%20then%20updating%20the%20denominators%20to%20the%20D11%2C%20E11%2C%20F11%20cell%20by%20cell%20(ugghh)%20AND%20lock%20them%20down%20so%20Excel%20doesn't%20ever-so-helpfully%20change%20the%20equations%20when%20my%20back%20is%20turned.%20But%20I%20can't%20figure%20out%20how%20to%20do%20this%20a%20whole%20block%20at%20a%20time.%3C%2FP%3E%3CP%3EI%20hope%20this%20is%20making%20sense...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2304103%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2304470%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20update%20copied%20cells%20to%20relative%20and%20absolute%20references%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2304470%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1039933%22%20target%3D%22_blank%22%3E%40Jackson_Barnett%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETwo%20methods.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Use%20%24D%2411%2C%20%24E%2411%20etc%20to%20lock%20them%20in%20place%20(F4%20is%20the%20quickest%20shortcut%20for%20that)%3C%2FP%3E%3CP%3E2.%20Name%20the%20cells%20in%20the%20wee%20box%20to%20the%20left%20of%20the%20formula%20bar.%20When%20you%20write%20the%20formula%20just%20reference%20the%20name%20you%20have%20given%20it%20eg%20A1%2Frng_Denominator%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi All,

I'm running
Windows 10 Home OS 64-bit Operating System, x64-based processor
Microsoft 365, ver. 2103, Build 13901.20400 Click-to-run 64-bit

I have a spreadsheet with which I track publicly-traded companies' earnings results.

One of the data points I track is what percentage of total revenue various measures equal.

The Total Revenue section is in a specific cell-range and I refer back to it time and again.

The other measures are obviously in different cell-ranges; but the denominators are always D11, E11, F11, and so on.

THE PROBLEM I'M TRYING TO SOLVE is how to copy the equations from one section to the next, and letting the numerators update as relative references, but locking the denominators down so they always refer back to D11, E11, F11, etc. ALL AT ONCE.

I've tried copying the equations as a block with relative references, then updating the denominators to the D11, E11, F11 cell by cell (ugghh) AND lock them down so Excel doesn't ever-so-helpfully change the equations when my back is turned. But I can't figure out how to do this a whole block at a time.

I hope this is making sense...

 

1 Reply

@Jackson_Barnett.

 

Two methods.

 

1. Use $D$11, $E$11 etc to lock them in place (F4 is the quickest shortcut for that)

2. Name the cells in the wee box to the left of the formula bar. When you write the formula just reference the name you have given it eg A1/rng_Denominator

 

also if you are need to do a block at a time, you could run a find and replace (including formulas) to look for d11 and replace with $D$11 etc