Forum Discussion
Changing cell reference in a formula using contents of another cell
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.
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.
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.
