One change affects several other cellrange references

  • Thread starter Thread starter JMay
  • Start date Start date
J

JMay

In my sheets I monthly must "adjust" the range of a SubTotal() formula
from say Subtotal(9,G5:G2500) to Subtotal(9,G5:G3100). There are
4 other Subtotals on the same sheet that need the same changes made...
that is from rows 5:2500 to 5:3100. Can I do something that once I change
the
(Call it) Master Cell to G5:G3100 the other 4 cell formulas also change to
the
same rows (that is, 5:3100) without having to manually change each?
TIA,
 
I like to put those subtotals in row 1. Then I just use the biggest range that
I ever think I'll need.

=subtotal(9,g3:g65536)

(row 1 has the subtotals (always visible, too!). Row 1 has the headers with
filters applied. I filter and I can see the results pretty darn fast.)
 
Same as that Sum formula JMay:

=SUBTOTAL(9,INDIRECT("G5:G"&B1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


In my sheets I monthly must "adjust" the range of a SubTotal() formula
from say Subtotal(9,G5:G2500) to Subtotal(9,G5:G3100). There are
4 other Subtotals on the same sheet that need the same changes made...
that is from rows 5:2500 to 5:3100. Can I do something that once I change
the
(Call it) Master Cell to G5:G3100 the other 4 cell formulas also change to
the
same rows (that is, 5:3100) without having to manually change each?
TIA,
 
***Max Window before reading***
This is not quite the same. Once I change my Master Formula range only in
cell B1 from
Subtotal(9,G5:G2500) to Subtotal(9,G5:G3100) I am looking for a way to
auto-change
Cell from
to
D1 Sumproduct(D5:D2500......K5:K2500) Sumproduct(D5:D3100....K5:K3100)
F1 Similar
J1 Similar

It's the Bottom-Range Row# (3100) that I want to extract from my master
formula and read into my Cells D1, F1 and J1.
TIA,
 
OK, Unless you are saying "Why not just put the 3100 in a clean cell to
itself and reference **ALL** cells to it, including your Master Cell [B1];
hummmmmm
I'll do that,,, hummmmm Thanks for your valuable input RagDyer !!
 
Yes. that's what I meant.
Sorry I didn't fully explain.
It was late, and I wanted to get the heck out of the plant!

Otherwise, guessing at your "4 other subtotals", I might have posted:

=SUBTOTAL(9,INDIRECT("G5:G"&B1))
=SUBTOTAL(9,INDIRECT("H5:H"&B1))
=SUBTOTAL(9,INDIRECT("I5:I"&B1))
=SUBTOTAL(9,INDIRECT("J5:J"&B1))

Which would have perhaps better illustrated the procedure.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

OK, Unless you are saying "Why not just put the 3100 in a clean cell to
itself and reference **ALL** cells to it, including your Master Cell [B1];
hummmmmm
I'll do that,,, hummmmm Thanks for your valuable input RagDyer !!
 
again, much appreciated.
JMay


RagDyer said:
Yes. that's what I meant.
Sorry I didn't fully explain.
It was late, and I wanted to get the heck out of the plant!

Otherwise, guessing at your "4 other subtotals", I might have posted:

=SUBTOTAL(9,INDIRECT("G5:G"&B1))
=SUBTOTAL(9,INDIRECT("H5:H"&B1))
=SUBTOTAL(9,INDIRECT("I5:I"&B1))
=SUBTOTAL(9,INDIRECT("J5:J"&B1))

Which would have perhaps better illustrated the procedure.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

OK, Unless you are saying "Why not just put the 3100 in a clean cell to
itself and reference **ALL** cells to it, including your Master Cell [B1];
hummmmmm
I'll do that,,, hummmmm Thanks for your valuable input RagDyer !!


***Max Window before reading***
This is not quite the same. Once I change my Master Formula range only in
cell B1 from
Subtotal(9,G5:G2500) to Subtotal(9,G5:G3100) I am looking for a way to
auto-change
Cell from
to
D1 Sumproduct(D5:D2500......K5:K2500) Sumproduct(D5:D3100....K5:K3100)
F1 Similar
J1 Similar

It's the Bottom-Range Row# (3100) that I want to extract from my master
formula and read into my Cells D1, F1 and J1.
TIA,
Click to expand...
change
to
Click to expand...
Click to expand...
 
Back
Top