Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find data in worksheet area that constantly changes
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find data in worksheet area that constantly changes
The example above should have looked more like this :-
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 "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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find data in worksheet area that constantly changes
It looks like after a lot of different formulas, I have found my fix in a roundabout way. By downloading the required information and offsetting it by one column which I have numbered sequentially, I can then get Excel to find the information such as the exact item number or name of stock then have it look over a set number of columns to get what I want. =IF(I7=0,0,OFFSET(A54,MATCH(I7,$A$55:$A$150,FALSE) ,2,1,1)) A54 = the minimum starting point for the info Match(I7) = the product name or no. A55:A150 = the maximum area where the info is kept Thanks to anyone who gave this matter thought. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find a worksheet and extract data | Excel Worksheet Functions | |||
find a worksheet and extract data | Excel Worksheet Functions | |||
find hidden data in worksheet | Excel Worksheet Functions | |||
find hidden data in worksheet | Excel Worksheet Functions | |||
Is Excel good for storing data that constantly updates? | Excel Discussion (Misc queries) |