range construction

[Gixxer_J_97]

Hi all,

consider this

A B C D E F G
1 N1 N2 N3 N4 N5 N6 N7
2 X1 X2 X3 X4 X5 X6 X7
3
4 M1 M2 M3 M4 M5 M6 M7
5 Y1 Y2 Y3 Y4 Y5 Y6 Y7
6

N and M are headers
X and Y are data
there are an indeterminate number of data rows, but they are always in pairs
ie after the next addition it will look like this

A B C D E F G
1 N1 N2 N3 N4 N5 N6 N7
2 X1 X2 X3 X4 X5 X6 X7
3 W1 W2 W3 W4 W5 W6 W7
4
5 M1 M2 M3 M4 M5 M6 M7
6 Y1 Y2 Y3 Y4 Y5 Y6 Y7
7 Z1 Z2 Z3 Z4 Z5 Z6 Z7

I would like to use the index function to define 2 ranges from this data
the first range will be from B1:G?
the second from B?:G?

using this
=$B$1:index($G:$G,counta($G:$G),1)
works somewhat for the top row - although it grabs too much
1 row of data in each section + 1 header in each section - 1Rx6C (header row
only)
2 rows - 3Rx6C (this one is ok)
3 rows - 5Rx6C (should be 4R)
4 rows - 7Rx6C (should be 5R)
5 rows - 9Rx6C (should be 6R)

i had expected it to define
(#of used cell in column G)R x 6 C but it's not
at 1 row of data there are 4 rows
2 rows - 6 rows used
3 rows - 8 rows used
etc

can anyone point out what i'm doing wrong and direct me to how i'd define
the range for the 2nd set of data? i would like to try not to use the OFFSET
function if at all possible. (there is nothing above or below this data, i
have selected the entire sheet, deleted, cleared contents.)

tia

J

 

[Tushar Mehta]

And the problem with the OFFSET function is...

 

[David]

Hi,
Ultimately what are you going to do with the data, if you find each section?
It would be possible to walk down the sheet and find the "full" address range
of each section, would this help you accomplish your purpose? What might be
the maximum number of "groups," can this be known?

 

[Gixxer_J_97]

offset is volatile and slows my calculations down. i also think that it's
the source of one of the problems i am having. otherwise it's a great
function =)

 

[Gixxer_J_97]

Hi David,

there are 24 total tables like this, one for each month, for 2 warehouses
the first range (like i described below). the first (top) range would be
named March_A, and the second (lower) would be named (March_1), the second
set would be April_A, and April_1 respectively, etc etc.

this sheet is used for inventory control for two warehouses. all of this
data will be 'hidden' from the user. on the same sheet i have a duplicate of
one of these sections, where the user chooses the month from a drop-down data
validation box, and the table populates by using the named range and the
vlookup function (one for the _A section, one for the _1 section)