Forum Discussion

Copper Contributor

Apr 24, 2019

Solved

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.

Craig_Gallagher
Copper Contributor
Apr 25, 2019
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.
- Twifoo
  Silver Contributor
  Apr 25, 2019
  You’re welcome.
- Craig_Gallagher
  Copper Contributor
  Apr 25, 2019
  I meant have it only increment once every 300 rows.