Capturing dynamic ranges
the individual totals are not distinguishable
TOTAL NORTH
TOTAL SOUTH
TOTAL EAST
TOTAL WEST
What do those mean?
--
Biff
Microsoft Excel MVP
"Paul Martin" wrote in message
...
As a slight refinement, the individual totals are not distinguishable,
but I can find the end of one range by a row offset (-1) from the
start of the next range. How would I deal with the end of the last
range?
Paul
On Jan 22, 2:24 pm, "T. Valko" wrote:
You can define each range like this...
For North:
=INDEX(AllData,MATCH("north",AllData,0)):INDEX(All Data,MATCH("total
north",AllData,0))
So, North will refer to:
NORTH
Apples
Pears
Oranges
TOTAL NORTH
--
Biff
Microsoft Excel MVP
"Paul Martin" wrote in message
...
Hi all
I've worked with dynamic ranges quite a bit and am generally fine with
them but now I have a slightly sticky scenario and my formulas are
getting a little long. I'm thinking there'll be a simpler solution
than mine. Here's the problem.
I have a dynamic range in a single column, let's call it AllData, and
is structured like this:
ALLDATA_START
NORTH
Apples
Pears
Oranges
TOTAL NORTH
SOUTH
Apples
Pears
Oranges
TOTAL SOUTH
EAST
Apples
Pears
Oranges
TOTAL EAST
WEST
Apples
Pears
Oranges
TOTAL WEST
ALLDATA_END
Given that AllData is defined, I'd like to create named ranges for
North, South, East and West. Again, these ranges need to be dynamic,
and my thinking is that each is an offset of AllData, based on the
position of the first and last lines of each of N, S, E & W.
Any suggestions appreciated. TIA.
Paul Martin
Melbourne, Australia
|