# Query in Excel

#### Herald

##### New Member

My name is Herald Dsouza. I hope this e-mail finds you in good condition.

I am preparing a sales report. I have situations wherein the customers place the order in advance and we book the order by providing them the reserved invoice number.
The actual sale will be on the next day or after a day.
Now my question is, while preparing the sales report, the future dated order also gets added to the current day's total sales amount, which is incorrect.
Do you have any formulas to exclude the future dated order from the total sales of the current day? (The future order will be added to the sales report on the day when the actual sale will happen in the future.)

Your valuable suggestion on the above-mentioned query is highly appreciated.

Awaiting for your response on this mail.

Thanks & Regards,
Herald Dsouza.

View attachment 61860View attachment 61860

#### Herald

##### New Member

Thank you for your e-mail. Please find the attached sample report in excel format for your report.

Thank you
Herald Dsouza.

#### Attachments

• 98.2 KB Views: 8

#### vletm

##### Excel Ninja
Herald
You wrote: Now my question is, while preparing the sales report, the future dated order also gets added to the current day's total sales amount, which is incorrect.
the 1st question: Where have You used terms 'the future date' or 'the current day' in Your workbook?
the 2nd questions: Why there are sheets for some days? Do You have to plan to have only those some days?
... Why not use Summary-sheet? You could get/see ALL days ... in same sheet (Summary).

#### GraH - Guido

##### Well-Known Member
One way...

Put a reference data in cell R1 (e.g. 31 July 2019)
Change your formula in B4, so to have dates, not datetime formatted as date. =IFERROR(INT(VLOOKUP(A4,Summary!A:C,3,0)),"-")
[N4] = IFERROR(IF(B4<>\$R\$1,N3,N3+F4-J4);N3)

#### Attachments

• 98.8 KB Views: 11

#### Cristex

##### New Member
I agree with vletim, about just using the one Summary sheet. What I would do with data is convert the entire range to an Excel Table and add the built-in Total Row. Then you can use the Filter on the date field you are reporting sales from. I noticed when I used the filter on a date column that Excel automatically breaks the filter down by Years and Months. If you use the filter check boxes to show only the date you are interested in, the total should show for just the selected date.

#### Herald

##### New Member
One way...

Put a reference data in cell R1 (e.g. 31 July 2019)
Change your formula in B4, so to have dates, not datetime formatted as date. =IFERROR(INT(VLOOKUP(A4,Summary!A:C,3,0)),"-")
[N4] = IFERROR(IF(B4<>\$R\$1,N3,N3+F4-J4);N3)
Thank you very much for your valuable suggestion. It helped me a lot.

Regards
Herald Dsouza

#### Herald

##### New Member
I agree with vletim, about just using the one Summary sheet. What I would do with data is convert the entire range to an Excel Table and add the built-in Total Row. Then you can use the Filter on the date field you are reporting sales from. I noticed when I used the filter on a date column that Excel automatically breaks the filter down by Years and Months. If you use the filter check boxes to show only the date you are interested in, the total should show for just the selected date.
Dear Cristex,
Thank you for your feedback. I am preparing the report, based on the invoice number and I want to keep a track of all the invoices. At times, when I have a future order, the actual sale will occur in the future and not today. If I filter the excel by date column, then the future dated invoice number gets hidden.
Once again, Thank you very much for your time & valuable suggestion.

Regards,
Herald Dsouza

#### Herald

##### New Member
Herald
You wrote: Now my question is, while preparing the sales report, the future dated order also gets added to the current day's total sales amount, which is incorrect.
the 1st question: Where have You used terms 'the future date' or 'the current day' in Your workbook?
the 2nd questions: Why there are sheets for some days? Do You have to plan to have only those some days?
... Why not use Summary-sheet? You could get/see ALL days ... in same sheet (Summary).
Dear vletm,

Thank you for your feedback. I am preparing the report, based on the invoice number and I want to keep a track of all the invoices. If I filter the date column in the Summary sheet, then the cancelled invoice and future dated invoices will get hidden in the sheet. As a result, I will not be able to track the invoice numbers.

Once again, Thank you very much for your time & valuable suggestion.

Regards,
Herald Dsouza

#### vletm

##### Excel Ninja
Herald
You skipped the 1st question - okay.

The 2nd ... why daily sheets? ... You'll two months later have 60 more same kind of sheets there... four months later again 60 more sheets there!
Then You'll be a master idea that You have to change something from those sheets ... that means, You have to change something to 120 sheets!

Based the invoice number - with Summary-sheet You could keep a track of ALL THE INVOICES - not with monthly sheets!
If You filter someway ... You could view Your needed rows.
With monthly sheets ... there are more 'hidden rows' ... and
... I'm 99% sure that You can see ONLY ONE SHEET in ONE TIME or how?

... one year later ... You'll have there ~365 more sheets ... same layout sheets instead of ONE sheet
- and You won't see more than one sheet in time.

Only Your files size would get bigger and bigger ...

#### Herald

##### New Member
One way...

Put a reference data in cell R1 (e.g. 31 July 2019)
Change your formula in B4, so to have dates, not datetime formatted as date. =IFERROR(INT(VLOOKUP(A4,Summary!A:C,3,0)),"-")
[N4] = IFERROR(IF(B4<>\$R\$1,N3,N3+F4-J4);N3)
Dear GraH - Guido,

I need your help with one more question.

In the attached sample book, Column B has different groups of order types. I need the individual categories (Example: Talabat Cash) to be automatically captured under Column F, along with invoice numbers and order amounts under column G and Column I respectively. Please advise which formula will be the best suitable to resolve this concern.

Best Regards,
Herald Dsouza.

#### Attachments

• 30.5 KB Views: 10

#### GraH - Guido

##### Well-Known Member
You have uploaded a .xls file. Does that mean it needs to work for a version before 2007?
If you have 2010 or later, I can suggest using aggregate function as it deals with arrays without having to confirm with CTRL+SHIFT+ENTER.
If you'd need a solution for older version, it is possible, but I'm not able to write them without an example.

#### Attachments

• 14.1 KB Views: 7

#### Herald

##### New Member
You have uploaded a .xls file. Does that mean it needs to work for a version before 2007?
If you have 2010 or later, I can suggest using aggregate function as it deals with arrays without having to confirm with CTRL+SHIFT+ENTER.
If you'd need a solution for older version, it is possible, but I'm not able to write them without an example.
Dear GraH - Guido,

Apologies for the misunderstanding while uploading the excel file. I tried to work on the formulas which you suggested. However, I could not succeed. I have uploaded a new excel file. Could you please assist me with the formulas in this excel sheet?

Appreciate your assistance on this request.

Regards
Herald Dsouza

#### Attachments

• 10.9 KB Views: 7

#### GraH - Guido

##### Well-Known Member
Hi, I've used table references. You need to replace those by range references. Though the advantage of table references is they are by default dynamic.
try
[G2]=IF(COUNTIF(\$B\$2:\$B\$125000,\$F\$1)<(ROW()-1),"",INDEX(B\$2:B\$125000,AGGREGATE(15,6,(ROW(B\$2:B\$125000)/(\$B\$2:\$B\$125000=\$F\$1)-1),ROW()-1)))