P
Paul Martin
Hi all
I have some dynamic ranges that are subsets of a master dynamic
ranges. The criteria for the start/end of each subset range are in
A1:A4
dynMaster = OFFSET($A$40, 0, 0, NumRows)
dynSub1 = INDEX(dynMaster, MATCH($A$1, dynMaster, 0)):INDEX(dynMaster,
MATCH($A$2, dynMaster, 0)-1)
dynSub2 = INDEX(dynMaster, MATCH($A$2, dynMaster, 0)):INDEX(dynMaster,
MATCH($A$3, dynMaster, 0)-1)
etc
These work fine. But now I want to be able to use a formula that
refers to the different subset data sources according to which column
the formula is used in.
DataSource = INDIRECT("dynSub" & COLUMN())
This doesn't work at all and I'm getting a REF error. I've noticed,
however, that if I hardcode the reference, ie INDIRECT("dynSub1"), it
works only when it's in the same row as dynSub1, but not where I need
to use the formula. Any suggestions are greatly appreciated.
TIA
Paul Martin
Melbourne, Australia
I have some dynamic ranges that are subsets of a master dynamic
ranges. The criteria for the start/end of each subset range are in
A1:A4
dynMaster = OFFSET($A$40, 0, 0, NumRows)
dynSub1 = INDEX(dynMaster, MATCH($A$1, dynMaster, 0)):INDEX(dynMaster,
MATCH($A$2, dynMaster, 0)-1)
dynSub2 = INDEX(dynMaster, MATCH($A$2, dynMaster, 0)):INDEX(dynMaster,
MATCH($A$3, dynMaster, 0)-1)
etc
These work fine. But now I want to be able to use a formula that
refers to the different subset data sources according to which column
the formula is used in.
DataSource = INDIRECT("dynSub" & COLUMN())
This doesn't work at all and I'm getting a REF error. I've noticed,
however, that if I hardcode the reference, ie INDIRECT("dynSub1"), it
works only when it's in the same row as dynSub1, but not where I need
to use the formula. Any suggestions are greatly appreciated.
TIA
Paul Martin
Melbourne, Australia