| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Capturing dynamic ranges
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Capturing dynamic ranges
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Capturing dynamic ranges
Great, thanks Biff
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Capturing dynamic ranges
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Capturing dynamic ranges
Hi Paul
just amend Biff's formula to =INDEX(AllData,MATCH("north",AllData,0)+1):INDEX(A llData,MATCH("total north",AllData,0)-1) North will then just refer to the Apples, Pears and Orange cells that are bounded by North and North Total You don't actually need to refer to the North Total cell, as =SUM(North) will give the same value -- Regards Roger Govier "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 __________ Information from ESET Smart Security, version of virus signature database 4798 (20100122) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4798 (20100122) __________ The message was checked by ESET Smart Security. http://www.eset.com |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Capturing dynamic ranges
Biff, the actual data doesn't have total lines that are differentiated
as per my example, ie, it's just "TOTAL". Paul On Jan 23, 3:57*am, "T. Valko" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Capturing dynamic ranges
Thanks, Roger. You've pre-empted my next question, which is a little
more complex. The source data is cut and pasted from a PDF and includes headers and footers which can interrupt the data. Consequently, one cannot be certain that the start and end of the data will be a set offset from the start and end of "AllData". So, I'd need something that identifies (in my example) the position of Apples (as the start of the data) and Oranges (as the end of the data). Any suggestions? Paul On Jan 23, 11:48*pm, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi Paul just amend Biff's formula to =INDEX(AllData,MATCH("north",AllData,0)+1):INDEX(A llData,MATCH("total north",AllData,0)-1) North will then just refer to the Apples, Pears and Orange cells that are bounded by North and North Total You don't actually need to refer to the North Total cell, as =SUM(North) will give the same value -- Regards Roger Govier "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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Capturing dynamic ranges
Another complexity is that there is an unknown string appending each
row. So it's not just 'Apples', but 'Apples ABC' in one place, 'Apples XYG' in another, etc. And because of the headers and footers, the data cannot be guaranteed to be sorted, ruling out VLOOKUP. Any suggestions are appreciated. So, to clarify, the data might look like this: ALLDATA_START NORTH Apples 197 Pears 83 <FOOTER <HEADER Oranges 176 TOTAL fclg SOUTH Apples 9 Pears 988 Oranges 15 TOTAL xnoa <FOOTER ALLDATA_END On Jan 27, 8:52*am, Paul Martin wrote: Thanks, Roger. *You've pre-empted my next question, which is a little more complex. *The source data is cut and pasted from a PDF and includes headers and footers which can interrupt the data. Consequently, one cannot be certain that the start and end of the data will be a set offset from the start and end of "AllData". *So, I'd need something that identifies (in my example) the position of Apples (as the start of the data) and Oranges (as the end of the data). *Any suggestions? Paul On Jan 23, 11:48*pm, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi Paul just amend Biff's formula to =INDEX(AllData,MATCH("north",AllData,0)+1):INDEX(A llData,MATCH("total north",AllData,0)-1) North will then just refer to the Apples, Pears and Orange cells that are bounded by North and North Total You don't actually need to refer to the North Total cell, as =SUM(North) will give the same value -- Regards Roger Govier "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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Capturing dynamic ranges
Well, at this point I'm lost!
-- Biff Microsoft Excel MVP "Paul Martin" wrote in message ... Another complexity is that there is an unknown string appending each row. So it's not just 'Apples', but 'Apples ABC' in one place, 'Apples XYG' in another, etc. And because of the headers and footers, the data cannot be guaranteed to be sorted, ruling out VLOOKUP. Any suggestions are appreciated. So, to clarify, the data might look like this: ALLDATA_START NORTH Apples 197 Pears 83 <FOOTER <HEADER Oranges 176 TOTAL fclg SOUTH Apples 9 Pears 988 Oranges 15 TOTAL xnoa <FOOTER ALLDATA_END On Jan 27, 8:52 am, Paul Martin wrote: Thanks, Roger. You've pre-empted my next question, which is a little more complex. The source data is cut and pasted from a PDF and includes headers and footers which can interrupt the data. Consequently, one cannot be certain that the start and end of the data will be a set offset from the start and end of "AllData". So, I'd need something that identifies (in my example) the position of Apples (as the start of the data) and Oranges (as the end of the data). Any suggestions? Paul On Jan 23, 11:48 pm, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi Paul just amend Biff's formula to =INDEX(AllData,MATCH("north",AllData,0)+1):INDEX(A llData,MATCH("total north",AllData,0)-1) North will then just refer to the Apples, Pears and Orange cells that are bounded by North and North Total You don't actually need to refer to the North Total cell, as =SUM(North) will give the same value -- Regards Roger Govier "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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Capturing dynamic ranges
I'll start a new post, Biff. Maybe I'm confusing the issue.
Thanks Paul On Jan 27, 12:56*pm, "T. Valko" wrote: Well, at this point I'm lost! -- Biff Microsoft Excel MVP "Paul Martin" wrote in message ... Another complexity is that there is an unknown string appending each row. *So it's not just 'Apples', but 'Apples ABC' in one place, 'Apples XYG' in another, etc. *And because of the headers and footers, the data cannot be guaranteed to be sorted, ruling out VLOOKUP. Any suggestions are appreciated. *So, to clarify, the data might look like this: ALLDATA_START NORTH Apples 197 Pears 83 <FOOTER <HEADER Oranges 176 TOTAL fclg SOUTH Apples 9 Pears 988 Oranges 15 TOTAL xnoa <FOOTER ALLDATA_END On Jan 27, 8:52 am, Paul Martin wrote: Thanks, Roger. You've pre-empted my next question, which is a little more complex. The source data is cut and pasted from a PDF and includes headers and footers which can interrupt the data. Consequently, one cannot be certain that the start and end of the data will be a set offset from the start and end of "AllData". So, I'd need something that identifies (in my example) the position of Apples (as the start of the data) and Oranges (as the end of the data). Any suggestions? Paul On Jan 23, 11:48 pm, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi Paul just amend Biff's formula to =INDEX(AllData,MATCH("north",AllData,0)+1):INDEX(A llData,MATCH("total north",AllData,0)-1) North will then just refer to the Apples, Pears and Orange cells that are bounded by North and North Total You don't actually need to refer to the North Total cell, as =SUM(North) will give the same value -- Regards Roger Govier "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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with dynamic ranges | New Users to Excel | |||
| Dynamic Ranges | Excel Discussion (Misc queries) | |||
| Dynamic ranges | Charts and Charting in Excel | |||
| dynamic ranges | Excel Worksheet Functions | |||
| Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions | |||
![Paul Martin[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
