Dynamically linked the references used in different cells

0

0-0 Wai Wai ^-^

Hi.
How can I dynamically linked the references / values etc. used in different
cells?

Eg:
There're many cells which use the same cell references:
A100 =Countif(A1:A100, ">5")
B19 =sum(A1:A100)
C52 =Countif(D1:D9, ">5")
and so on

Is it possible to put the reference "A1:A100" & ">5" in other cells, so when
these values change, all formulas which uses these values will be dynamically
updated?

I know I can do put ">5" (without quotes) in one cell (eg S1):
5
Click to expand...

Then I can dynamically linked by rewriting different formulas:
A100 =Countif(A1:A100, S1)
B19 =sum(A1:A100)
C52 =Countif(D1:D9, S1)

But how to do the "A1:A100" bits?

If the above idea is impossible, any workaround is still appreciated.
 
R

RagDyeR

Try this:

=SUM(INDIRECT(D1&":"&D2))

Where A1 is in D1, and A100 is in D2.
--

HTH,

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

Hi.
How can I dynamically linked the references / values etc. used in different
cells?

Eg:
There're many cells which use the same cell references:
A100 =Countif(A1:A100, ">5")
B19 =sum(A1:A100)
C52 =Countif(D1:D9, ">5")
and so on

Is it possible to put the reference "A1:A100" & ">5" in other cells, so when
these values change, all formulas which uses these values will be
dynamically
updated?

I know I can do put ">5" (without quotes) in one cell (eg S1):
5
Click to expand...

Then I can dynamically linked by rewriting different formulas:
A100 =Countif(A1:A100, S1)
B19 =sum(A1:A100)
C52 =Countif(D1:D9, S1)

But how to do the "A1:A100" bits?

If the above idea is impossible, any workaround is still appreciated.
 
0

0-0 Wai Wai ^-^

RagDyeR said:
Try this:

=SUM(INDIRECT(D1&":"&D2))

Where A1 is in D1, and A100 is in D2.
Click to expand...

Thanks.
The function INDIRECT(D1&":"&D2) can be applied to other formulas too.
Thanks a lot.

By the way, it seems I need to put in the INDIRECT formula in each formula I
use.
But I can't put the INDIRECT formula in its separate cell, and reference to this
cell in each formula.

Eg:
=SUM(E1)

where E1 contains =INDIRECT(D1&":"&D2)

{need to do further test for confirmation}
 
Top