Way to make number portion of cell reference a variable?

chunone3853

New Member
Joined
Mar 4, 2023
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
I have a formula that references D9, G9, J9, K9 and M9


Over time that will change to D10, G10, J10, K10, and M10

Then 11. Then 12. Etc This change occurs every five days, and I'd rather update one cell, that five cells each with a complicated formula.

Is there a way to have the "9" portion of the address reference another cells value, so I only need to update that one cell?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
You'll have to adjust your formulas, but with the broken ranges I just did it easy and made contiguous ranges. But the
Excel Formula:
INT((row(B9)-4)/5)

portion of the formula allows you to keep the same value for every 5 rows.


Book1
ABCDEFG
92023-01-01142345
102023-01-02143456
112023-01-03144567
122023-01-04145678
132023-01-0514
142023-01-0618
152023-01-0718
162023-01-0818
172023-01-0918
182023-01-1018
192023-01-1122
202023-01-1222
212023-01-1322
222023-01-1422
232023-01-1522
242023-01-1626
252023-01-1726
262023-01-1826
272023-01-1926
282023-01-2026
Sheet6
Cell Formulas
RangeFormula
B9:B28B9=SUM((INDEX($D$9:$G$12,INT((ROW(B9)-4)/5),1)):(INDEX($D$9:$G$12,INT((ROW(B9)-4)/5),4)))
 
Upvote 0
I you use code, you should be able to handle any number of rows?
 
Upvote 0
I have a formula that references D9, G9, J9, K9 and M9


Over time that will change to D10, G10, J10, K10, and M10

Then 11. Then 12. Etc This change occurs every five days, and I'd rather update one cell, that five cells each with a complicated formula.

Is there a way to have the "9" portion of the address reference another cells value, so I only need to update that one cell?
Perhaps I wasn't being clear, I want a cell say A5 that I could put in a value 9 (or 10, 11, 12 etc) such that the references inside of various cells, change to D9, or D10, or D11. Making a table large enough to encompass 7 references 73 times per year would be a pain.
 
Upvote 0
oh, you want to build the formula?
You would use the indirect statement. But, if you are goint to have lots of indirect statements then your worksheet performance will degrade.

but, given that. how do you determine when you want to recalculate the formula? Just the last row in the date range column?
 
Upvote 0
oh, you want to build the formula?
You would use the indirect statement. But, if you are goint to have lots of indirect statements then your worksheet performance will degrade.

but, given that. how do you determine when you want to recalculate the formula? Just the last row in the date range column?
Indirect worked, thank you
 
Upvote 0
Great, how are you calculating that in the single formula for the various dates? Are you using row() calculation?
 
Upvote 0
Indirect worked,
Welcome to thee MrExcel board!

So long as you are aware that INDIRECT is a volatile function and can slow your sheet performance if used too much.
In general, I try to avoid volatile functions if an alternative is possible. An alternative may be possible for you if you are interested. However, to determine if there is a feasible alternative, we would need to know what your current actual formulas are.
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top