I download data to a spreadsheet and from this I must extract data from
different areas. The problem is that the data that is downloaded is not
always in the same place as each salesperson must type in anything from 3 to
40 (approx) lines of information from their region, which is used by someone
else.
The sales figures are always below the typed information of varying size.
Other problems that exist are that not all salespersons sell the same stock
each month, it varies region to region. Example below.

Monthly Product Break-up
Order In On Hand Sold
$ Returns Item No. Jan 50 100
35 175 2
(or Name) Feb 60 125 40
200 0
March 40 125 55
275 3

Have tried combinations of "match", "index", "offset", "getpivotdata". Some
don't seem to work together inside each formula.

Heading "Monthly Product Break-up" is always there on an everchanging row
number. The data required is always a set number of columns to the right for
each one.

The list of "item no.s" wont be in the same order for each salesperson.

There are more column headings than I have displayed above and some are just
a letter or a number.

I have been copying and pasting the data which works OK but can be time
consuming.

How do I get Excel 2003 to find the heading "Monthly Product Break-up" and
work down from there looking up certain product numbers or names then go
across to find the data in particular columns?

Any help offered is greatly appreciated.

The "EXAMPLE" didn't come out looking like it should, "Monthly Product
Break-up" is on 1st line.
"Order In, On Hand, Sold, $, Returns" on 2nd line.
"Item No., Name, Month, figures for each of 2nd line" on 3rd line etc.

"Foggy1" wrote:

I download data to a spreadsheet and from this I must extract data from
different areas. The problem is that the data that is downloaded is not
always in the same place as each salesperson must type in anything from 3 to
40 (approx) lines of information from their region, which is used by someone
else.
The sales figures are always below the typed information of varying size.
Other problems that exist are that not all salespersons sell the same stock
each month, it varies region to region. Example below.

Monthly Product Break-up
Order In On Hand Sold
$ Returns Item No. Jan 50 100
35 175 2
(or Name) Feb 60 125 40
200 0
March 40 125 55
275 3

Have tried combinations of "match", "index", "offset", "getpivotdata". Some
don't seem to work together inside each formula.

Heading "Monthly Product Break-up" is always there on an everchanging row
number. The data required is always a set number of columns to the right for
each one.

The list of "item no.s" wont be in the same order for each salesperson.

There are more column headings than I have displayed above and some are just
a letter or a number.

I have been copying and pasting the data which works OK but can be time
consuming.

How do I get Excel 2003 to find the heading "Monthly Product Break-up" and
work down from there looking up certain product numbers or names then go
across to find the data in particular columns?

Any help offered is greatly appreciated.

If you're using combinations of MATCH, INDEX etc, then you probably want to
post this in the Excel Worksheet Functions forum rather than here. Here
you'll get people trying to tell you what you should put in a VBA program to
do the same job.

On the other hand, it looks a little to me like that may be what you need.
Do you know how to write VBA code? generally speaking, I mean?

--- "Foggy1" wrote:
I download data to a spreadsheet and from this I must extract data from
different areas. The problem is that the data that is downloaded is not
always in the same place as each salesperson must type in anything from 3 to
40 (approx) lines of information from their region, which is used by someone
else.
The sales figures are always below the typed information of varying size.
Other problems that exist are that not all salespersons sell the same stock
each month, it varies region to region. Example below.

Monthly Product Break-up
Order In On Hand Sold
$ Returns Item No. Jan 50 100
35 175 2
(or Name) Feb 60 125 40
200 0
March 40 125 55
275 3

Have tried combinations of "match", "index", "offset", "getpivotdata". Some
don't seem to work together inside each formula.

Heading "Monthly Product Break-up" is always there on an everchanging row
number. The data required is always a set number of columns to the right for
each one.

The list of "item no.s" wont be in the same order for each salesperson.

There are more column headings than I have displayed above and some are just
a letter or a number.

I have been copying and pasting the data which works OK but can be time
consuming.

How do I get Excel 2003 to find the heading "Monthly Product Break-up" and
work down from there looking up certain product numbers or names then go
across to find the data in particular columns?

Thanks Bob, have not learnt how to write VBA code yet, that may be an option
down the track if I can't get it to work with some formulas with fancy
functions. I will put this into the Functions forum as you suggest.

"Bob Bridges" wrote:

If you're using combinations of MATCH, INDEX etc, then you probably want to
post this in the Excel Worksheet Functions forum rather than here. Here
you'll get people trying to tell you what you should put in a VBA program to
do the same job.

On the other hand, it looks a little to me like that may be what you need.
Do you know how to write VBA code? generally speaking, I mean?

--- "Foggy1" wrote:
I download data to a spreadsheet and from this I must extract data from
different areas. The problem is that the data that is downloaded is not
always in the same place as each salesperson must type in anything from 3 to
40 (approx) lines of information from their region, which is used by someone
else.
The sales figures are always below the typed information of varying size.
Other problems that exist are that not all salespersons sell the same stock
each month, it varies region to region. Example below.

Monthly Product Break-up
Order In On Hand Sold
$ Returns Item No. Jan 50 100
35 175 2
(or Name) Feb 60 125 40
200 0
March 40 125 55
275 3

Have tried combinations of "match", "index", "offset", "getpivotdata". Some
don't seem to work together inside each formula.

Heading "Monthly Product Break-up" is always there on an everchanging row
number. The data required is always a set number of columns to the right for
each one.

The list of "item no.s" wont be in the same order for each salesperson.

There are more column headings than I have displayed above and some are just
a letter or a number.

I have been copying and pasting the data which works OK but can be time
consuming.

How do I get Excel 2003 to find the heading "Monthly Product Break-up" and
work down from there looking up certain product numbers or names then go
across to find the data in particular columns?

Hi,

I can't fathom the data layout. I think you would have a beter chance if you
posted some sample data and then posted the link.

You can do that for free here
http://www.savefile.com/

Mike

"Foggy1" wrote:

Thanks Bob, have not learnt how to write VBA code yet, that may be an option
down the track if I can't get it to work with some formulas with fancy
functions. I will put this into the Functions forum as you suggest.

"Bob Bridges" wrote:

If you're using combinations of MATCH, INDEX etc, then you probably want to
post this in the Excel Worksheet Functions forum rather than here. Here
you'll get people trying to tell you what you should put in a VBA program to
do the same job.

On the other hand, it looks a little to me like that may be what you need.
Do you know how to write VBA code? generally speaking, I mean?

--- "Foggy1" wrote:
I download data to a spreadsheet and from this I must extract data from
different areas. The problem is that the data that is downloaded is not
always in the same place as each salesperson must type in anything from 3 to
40 (approx) lines of information from their region, which is used by someone
else.
The sales figures are always below the typed information of varying size.
Other problems that exist are that not all salespersons sell the same stock
each month, it varies region to region. Example below.

Monthly Product Break-up
Order In On Hand Sold
$ Returns Item No. Jan 50 100
35 175 2
(or Name) Feb 60 125 40
200 0
March 40 125 55
275 3

Have tried combinations of "match", "index", "offset", "getpivotdata". Some
don't seem to work together inside each formula.

Heading "Monthly Product Break-up" is always there on an everchanging row
number. The data required is always a set number of columns to the right for
each one.

The list of "item no.s" wont be in the same order for each salesperson.

There are more column headings than I have displayed above and some are just
a letter or a number.

I have been copying and pasting the data which works OK but can be time
consuming.

How do I get Excel 2003 to find the heading "Monthly Product Break-up" and
work down from there looking up certain product numbers or names then go
across to find the data in particular columns?

Hi Mike,

It would hard when all the spaces get taken out and the data does not line up.

I have done as you requested and the link is below to the sample spreadsheet.

http://savefile.com/projects/808679988

"Mike H" wrote:

Hi,

I can't fathom the data layout. I think you would have a beter chance if you
posted some sample data and then posted the link.

You can do that for free here
http://www.savefile.com/

Mike

"Foggy1" wrote:

Thanks Bob, have not learnt how to write VBA code yet, that may be an option
down the track if I can't get it to work with some formulas with fancy
functions. I will put this into the Functions forum as you suggest.

"Bob Bridges" wrote:

If you're using combinations of MATCH, INDEX etc, then you probably want to
post this in the Excel Worksheet Functions forum rather than here. Here
you'll get people trying to tell you what you should put in a VBA program to
do the same job.

On the other hand, it looks a little to me like that may be what you need.
Do you know how to write VBA code? generally speaking, I mean?

--- "Foggy1" wrote:
I download data to a spreadsheet and from this I must extract data from
different areas. The problem is that the data that is downloaded is not
always in the same place as each salesperson must type in anything from 3 to
40 (approx) lines of information from their region, which is used by someone
else.
The sales figures are always below the typed information of varying size.
Other problems that exist are that not all salespersons sell the same stock
each month, it varies region to region. Example below.

Monthly Product Break-up
Order In On Hand Sold
$ Returns Item No. Jan 50 100
35 175 2
(or Name) Feb 60 125 40
200 0
March 40 125 55
275 3

Have tried combinations of "match", "index", "offset", "getpivotdata". Some
don't seem to work together inside each formula.

Heading "Monthly Product Break-up" is always there on an everchanging row
number. The data required is always a set number of columns to the right for
each one.

The list of "item no.s" wont be in the same order for each salesperson.

There are more column headings than I have displayed above and some are just
a letter or a number.

I have been copying and pasting the data which works OK but can be time
consuming.

How do I get Excel 2003 to find the heading "Monthly Product Break-up" and
work down from there looking up certain product numbers or names then go
across to find the data in particular columns?

Sorry Mike, file will not upload, will keep trying.

"Mike H" wrote:

Hi,

I can't fathom the data layout. I think you would have a beter chance if you
posted some sample data and then posted the link.

You can do that for free here
http://www.savefile.com/

Mike

"Foggy1" wrote:

Thanks Bob, have not learnt how to write VBA code yet, that may be an option
down the track if I can't get it to work with some formulas with fancy
functions. I will put this into the Functions forum as you suggest.

"Bob Bridges" wrote:

If you're using combinations of MATCH, INDEX etc, then you probably want to
post this in the Excel Worksheet Functions forum rather than here. Here
you'll get people trying to tell you what you should put in a VBA program to
do the same job.

On the other hand, it looks a little to me like that may be what you need.
Do you know how to write VBA code? generally speaking, I mean?

--- "Foggy1" wrote:
I download data to a spreadsheet and from this I must extract data from
different areas. The problem is that the data that is downloaded is not
always in the same place as each salesperson must type in anything from 3 to
40 (approx) lines of information from their region, which is used by someone
else.
The sales figures are always below the typed information of varying size.
Other problems that exist are that not all salespersons sell the same stock
each month, it varies region to region. Example below.

Monthly Product Break-up
Order In On Hand Sold
$ Returns Item No. Jan 50 100
35 175 2
(or Name) Feb 60 125 40
200 0
March 40 125 55
275 3

Have tried combinations of "match", "index", "offset", "getpivotdata". Some
don't seem to work together inside each formula.

Heading "Monthly Product Break-up" is always there on an everchanging row
number. The data required is always a set number of columns to the right for
each one.

The list of "item no.s" wont be in the same order for each salesperson.

There are more column headings than I have displayed above and some are just
a letter or a number.

I have been copying and pasting the data which works OK but can be time
consuming.

How do I get Excel 2003 to find the heading "Monthly Product Break-up" and
work down from there looking up certain product numbers or names then go
across to find the data in particular columns?

No go Mike, "SaveFile.com" appears to think that my 17.5kb file is in excess
of 60mb. I don't think that the site is working too well

"Foggy1" wrote:

Sorry Mike, file will not upload, will keep trying.