A nested formula needed: Sales reporting

**bimmer5dude** · 06-10-2008, 06:16 PM

I have a requirement to let the user of this spreadsheet to select a date or a range of dates and in return to get the sales report that will display the following:
- Product weight sold (LBS),
- Cost to manufacture the product (Cost) and
- Sales amounts.
Please take a look at the spreadsheet attached. It contains two sheets: a “Query1” sheet and a “Report” sheet. The Report sheet looks up the info from the Query1 sheet. The Query1 sheet will be updated daily from a database. The Report sheet contains three tables:
- Daily Analysis,
- Month-To-Date analysis and
- Year-To-Date analysis.
The user needs to input a single date above the “Daily” report and then it needs to input the monthly range of dates above the Month-To-Date report. The same is for the YTD report.
I am looking for a cell formula that will look at the date above (or the Start and End dates) and accordingly find the relevant information within the “Query1” sheet and return it to the appropriate cell on the report.
For a start, I had a success using similar formula:

=IF(ISNA(VLOOKUP("F01",'query1'!$A$2:$E$1000,2,FALSE)),0,VLOOKUP("F01",'query1'!$A$2:$E$1000,2,FALSE))

but I need to inject a nested date or a date range into the formula, which is a challange for me. The values displayed in the “Sample” workbook are the values for your reference only that we should get returned after applying the formula.
Any suggestions would be highly appreciated!
Thank you,
Peter

**Shijesh Kumar** · 06-10-2008, 07:09 PM

Hi ,

Try this

=SUMPRODUCT((query1!B2:B677=Report!A9)*(query1!A2:A677=Report!D2)*(query1!C2:C677))

this is to calculate LBS..
Note:== Please copy the date from query table to Report table in D2 so that the formula works effectively ..

**bimmer5dude** · 06-10-2008, 07:20 PM

Thank you for your reply.
Well, it works somehow for the first table but the requirement is to give the ability to the user to input the date in the Report's cell D2 as well to enter other dates in J2 and J3, etc... Please be advised that my real Report sheet will contain the product descriptions instead of coding F01, F02, etc on the left side. I put them on the left side for easier understanding.
Do you think we can do something using Advanced Filtering?

**Shijesh Kumar** · 06-10-2008, 07:48 PM

I have uploded your sample sheet with my formula.. please check...

Also... if your are typing the date manually in report sheet D2 then it will give zero... try to copy the date from query sheet and paste in the report sheet at D2

**bimmer5dude** · 06-10-2008, 07:59 PM

Shijesh,

you are right, it works that way for the first Daily table. However, my plan is to hide the Query sheet from the regular user to avoid any accidental data loss. I would be really happy if we can come up with something else by giving the user opportunitiy to type in the selection dates in the Report sheet.
Aside, do you have any idea how to handle other analysis tables with the date range?
Thank you for your continuous help in this matter.

**Shijesh Kumar** · 06-10-2008, 08:03 PM

Hi,

U can create a drop down list which takes the data from query sheet...
In this way the user will be able to select the date and the forumla will work fine...
This type for problem generally arises when u export the data from database like oracle 9i to excel.. their date don't match with the one in excel..