SOLVED
Home

Changing cell reference in a formula using contents of another cell

%3CLINGO-SUB%20id%3D%22lingo-sub-482154%22%20slang%3D%22en-US%22%3EChanging%20cell%20reference%20in%20a%20formula%20using%20contents%20of%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482154%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20simple%20formula%3A%26nbsp%3B%26nbsp%3B%3DD13801%2FCalibration!%24G%2448%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20first%20part%20is%20a%20cell%20reference%20on%20my%20current%20page.%26nbsp%3B%20What%20I%20need%20is%20for%20that%20digit%20at%20the%20end%2C%2048%20in%20this%20example%2C%20to%20be%20the%20contents%20of%20a%20different%20cell%2C%20so%20I%20don't%20have%20to%20type%20that%20number%20into%20each%20line%20as%20needed.%26nbsp%3B%20I'm%20using%20the%20same%20digit%20for%20three%20hundred%20consecutive%20lines%2C%20then%20it%20ticks%20up%20by%201%2C%20then%20another%20three%20hundred%20lines%2C%20etc.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20already%20have%20a%20column%20set%20up%20with%20the%20correct%20sequence%20of%20digits%20I%20want%20to%20appear%20at%20the%20end%20of%20the%20formula%20in%20each%20row%2C%20I%20just%20don't%20know%20how%20to%20use%20the%20contents%20of%20those%20cells%20in%20the%20formula.%26nbsp%3B%20Typing%20them%20in%20by%20hand%20on%20each%20line%20is%20far%20too%20time%20consuming%2C%20as%20they%20are%20many.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20thought%20I%20might%20be%20on%20the%20right%20track%20with%20the%20INDIRECT%20function%2C%20but%20couldn't%20get%20it%20to%20work.%26nbsp%3B%20Maybe%20my%20syntax%20is%20wrong%2C%20or%20maybe%20it%20doesn't%20actually%20work%20the%20way%20I%20think%20it%20does.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-482154%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482960%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20cell%20reference%20in%20a%20formula%20using%20contents%20of%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482960%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20welcome.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482942%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20cell%20reference%20in%20a%20formula%20using%20contents%20of%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482942%22%20slang%3D%22en-US%22%3EI%20meant%20have%20it%20only%20increment%20once%20every%20300%20rows.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482729%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20cell%20reference%20in%20a%20formula%20using%20contents%20of%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482729%22%20slang%3D%22en-US%22%3EYes%2C%20that%20worked.%20Thank%20you!%3CBR%20%2F%3E%3CBR%20%2F%3EI%20had%20been%20wondering%20if%20there%20were%20a%20way%20to%20just%20have%20the%20increment%2C%20as%20you%20go%20from%20one%20row%20to%20the%20next%2C%20be%20300%20instead%20of%201%2C%20and%20that's%20done%20it.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20will%20study%20the%20INDEX%20and%20CEILING%20functions%20to%20follow%20what%20you've%20done%20here.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482453%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20cell%20reference%20in%20a%20formula%20using%20contents%20of%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482453%22%20slang%3D%22en-US%22%3E%3CP%3EInstead%20of%20%3CSTRONG%3ED1%2FCalibration!%24G%242%3C%2FSTRONG%3E%2C%20use%20this%20formula%20in%20G1%2C%20copied%20down%20rows%3A%3CBR%20%2F%3E%3DD1%2F%3CSTRONG%3EINDEX(Calibration!G%3AG%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ECEILING(ROW()%2C300)%2F300%2B1)%3C%2FSTRONG%3E%3CBR%20%2F%3EThe%20divisor%20in%20the%20foregoing%20formula%20becomes%20%3CSTRONG%3ECalibration!G2%3C%2FSTRONG%3E%20for%20%3CSTRONG%3EG1%3AG300%3C%2FSTRONG%3E%2C%20%3CSTRONG%3ECalibration!G3%3C%2FSTRONG%3E%20for%20%3CSTRONG%3EG301%3AG600%3C%2FSTRONG%3E%2C%20%3CSTRONG%3ECalibration!G4%3C%2FSTRONG%3E%20for%20%3CSTRONG%3EG601%3AG900%3C%2FSTRONG%3E%2C%20and%20so%20forth.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482237%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20cell%20reference%20in%20a%20formula%20using%20contents%20of%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482237%22%20slang%3D%22en-US%22%3E%3CP%3EI%20already%20have%20the%20digits%20I%20want%20on%20the%20end%20of%20the%20formula%2C%20in%20the%20cells%20in%20column%20H%2C%20but%20I%20don't%20know%20how%20to%20concatenate%20those%20onto%20the%20end%20of%20the%20formulas%20in%20column%20G%20and%20have%20it%20accept%20it%20as%20part%20of%20the%20formula%20string.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482235%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20cell%20reference%20in%20a%20formula%20using%20contents%20of%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482235%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20responding.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20typed%20a%20sample%20formula%20into%20the%20original%20comment.%26nbsp%3B%20I%20just%20have%20that%20one%20to%20work%20with.%26nbsp%3B%20I%20will%20attach%20the%20file%20I'm%20working%20with%2C%20but%20there's%20a%20lot%20of%20other%20stuff%20in%20it.%26nbsp%3B%20If%20you%20look%20at%20the%20tab%20called%20%3CDATA%20300%3D%22%22%20counts%3D%22%22%3E%20and%20look%20down%20column%20G%2C%20you%20can%20see%20what%20I'm%20trying%20to%20do.%26nbsp%3B%20I've%20done%20a%20number%20of%20entries%20by%20brute%20force%2C%20but%20it%20would%20take%20me%20a%20long%20time%20to%20get%20through%20them%20all%20doing%20a%20copy%2Fpaste%20with%20a%20typed%20edit%20once%20each%20300%20rows.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20want%20the%20first%20cell%20(in%20the%20formula%20in%20column%20G%20on%20%3CDATA%20300%3D%22%22%20counts%3D%22%22%3E)%20to%20increment%20by%20one%20as%20I%20copy%20the%20formula%20down%20the%20column%2C%20but%20I%20only%20want%20the%20digit%20on%20the%20end%20to%20increment%20by%201%20once%20each%20300%20rows.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20for%20the%20help.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FDATA%3E%3C%2FDATA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482224%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20cell%20reference%20in%20a%20formula%20using%20contents%20of%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482224%22%20slang%3D%22en-US%22%3EYou%20must%20at%20least%20provide%20a%20list%20of%20your%20formulas%2C%20if%20you%20cannot%2C%20for%20any%20reason%2C%20attach%20your%20sample%20file.%3C%2FLINGO-BODY%3E
Craig_Gallagher
Occasional Contributor

I have a simple formula:  =D13801/Calibration!$G$48

The first part is a cell reference on my current page.  What I need is for that digit at the end, 48 in this example, to be the contents of a different cell, so I don't have to type that number into each line as needed.  I'm using the same digit for three hundred consecutive lines, then it ticks up by 1, then another three hundred lines, etc.

I already have a column set up with the correct sequence of digits I want to appear at the end of the formula in each row, I just don't know how to use the contents of those cells in the formula.  Typing them in by hand on each line is far too time consuming, as they are many.

I thought I might be on the right track with the INDIRECT function, but couldn't get it to work.  Maybe my syntax is wrong, or maybe it doesn't actually work the way I think it does.

Thank you.

7 Replies
Highlighted
You must at least provide a list of your formulas, if you cannot, for any reason, attach your sample file.

Thank you for responding.

I typed a sample formula into the original comment.  I just have that one to work with.  I will attach the file I'm working with, but there's a lot of other stuff in it.  If you look at the tab called <Data 300 Counts> and look down column G, you can see what I'm trying to do.  I've done a number of entries by brute force, but it would take me a long time to get through them all doing a copy/paste with a typed edit once each 300 rows.

I want the first cell (in the formula in column G on <Data 300 Counts>) to increment by one as I copy the formula down the column, but I only want the digit on the end to increment by 1 once each 300 rows.

Thank you for the help.


I already have the digits I want on the end of the formula, in the cells in column H, but I don't know how to concatenate those onto the end of the formulas in column G and have it accept it as part of the formula string.

Solution

Instead of D1/Calibration!$G$2, use this formula in G1, copied down rows:
=D1/INDEX(Calibration!G:G,
CEILING(ROW(),300)/300+1)
The divisor in the foregoing formula becomes Calibration!G2 for G1:G300, Calibration!G3 for G301:G600, Calibration!G4 for G601:G900, and so forth.

Yes, that worked. Thank you!

I had been wondering if there were a way to just have the increment, as you go from one row to the next, be 300 instead of 1, and that's done it.

I will study the INDEX and CEILING functions to follow what you've done here.
I meant have it only increment once every 300 rows.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies