Changing cell reference in a formula using contents of another cell

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 di...

excel

Formulas and Functions

Twifoo
Apr 25, 2019
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.

Craig_Gallagher

Copper Contributor

Apr 24, 2019

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.

Twifoo

Silver Contributor

Apr 25, 2019

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.

Forum Discussion

Changing cell reference in a formula using contents of another cell

Resources