Prev: How do I arrange cells in alphabetical order in Excel?
Next: How do i show the drop down arrow for a list in Excel
From: Paul Martin on 27 Jan 2010 19:45
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
 | 
Pages: 1
Prev: How do I arrange cells in alphabetical order in Excel?
Next: How do i show the drop down arrow for a list in Excel