Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Function - Date Problem
I have a list of sales information and I just want to extract a particular customer's sales but as individual lines, not a total. Currently got an If function running per line that returns either the info required or a 0, so I have to filter that sheet to hide the 0 lines. Ultimately I need to extract this info monthly to perform some calculations on. Having real trouble with the IF function to limit the results to particular months. Oh, and I'm still filtering at this point too!! Is there an easier way of doing this, perhaps in one function from the original sales sheet. Just to extract this customer's sales info per sale line into monthly tables? The info source is actually two sheets as well, just to make this worse. I've looked at pivot tables but they just seem to provide totals. Perhaps I could create a table that takes one month's worth of data from both sheets into a monthly total, completing all calculations at once? Any thoughts before I provide specifics of the functions I have running? I'm sure I'm just being a numpty! -- JEM ------------------------------------------------------------------------ JEM's Profile: http://www.excelforum.com/member.php...o&userid=35124 View this thread: http://www.excelforum.com/showthread...hreadid=548814 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Function - Date Problem
If your sheets are set up like databases - a single row of column headers in
row 1 and data commencing in row 2, then you should explore the DataImport External DataNew Database Query menu options. You can create a query or queries against your Excel sheets to pull specific rows of data into a new sheet or a 'master' calc sheet. Changing the query parameters to pull in the next set of data is relatively easy, too. "JEM" wrote: I have a list of sales information and I just want to extract a particular customer's sales but as individual lines, not a total. Currently got an If function running per line that returns either the info required or a 0, so I have to filter that sheet to hide the 0 lines. Ultimately I need to extract this info monthly to perform some calculations on. Having real trouble with the IF function to limit the results to particular months. Oh, and I'm still filtering at this point too!! Is there an easier way of doing this, perhaps in one function from the original sales sheet. Just to extract this customer's sales info per sale line into monthly tables? The info source is actually two sheets as well, just to make this worse. I've looked at pivot tables but they just seem to provide totals. Perhaps I could create a table that takes one month's worth of data from both sheets into a monthly total, completing all calculations at once? Any thoughts before I provide specifics of the functions I have running? I'm sure I'm just being a numpty! -- JEM ------------------------------------------------------------------------ JEM's Profile: http://www.excelforum.com/member.php...o&userid=35124 View this thread: http://www.excelforum.com/showthread...hreadid=548814 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Function - Date Problem
'The info source is actually two sheets as well, just to make this worse'. <<< a link in this page tells how to use a union statement to create a view that contains the data from both sheets. http://www.contextures.com/xlPivot08.html 'I've looked at pivot tables but they just seem to provide totals. Perhaps I could create a table that takes one month's worth of data from both sheets into a monthly total, completing all calculations at once?' <<<< if you double-click on any pivot-table value, a new worksheet will be created that displays all records in that total. Would that provide a solution? -- steven1001 ------------------------------------------------------------------------ steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757 View this thread: http://www.excelforum.com/showthread...hreadid=548814 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time problem | Excel Worksheet Functions | |||
Date problem using VBA | New Users to Excel | |||
date format problem | Excel Discussion (Misc queries) | |||
Date function | Excel Worksheet Functions | |||
Problem with Date format from VBA code | Excel Discussion (Misc queries) |