Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
range construction
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
range construction
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
range construction
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 =) "Tushar Mehta" wrote: And the problem with the OFFSET function is... -- Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
range construction
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) "David" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula construction | New Users to Excel | |||
Construction Industry | New Users to Excel | |||
Array Construction | Excel Discussion (Misc queries) | |||
Construction Templates | Excel Discussion (Misc queries) | |||
formula construction | Excel Programming |