# Revenue report depend on date



## forza fiume (Nov 18, 2022)

Dear Mr Excel,
of course, I have a problem.
In the hotel where I work, we have to make a daily report of sold souvenirs and other items. I solved columns K,L and M but I have a problem with column N. 
For now in cell N3 formula is =XLOOKUP(K3;$A$3:$A$301;$D$3:$D$301;0; 0) and that is fine for one day. 
My wish is that column N show value depending on the date chosen in N1( drop-down list). Which formula can choose D, E, F ...etc. for every day in a month

Daily report should be saved like a PDF to send to the bookkeeper.
Excel file must have a sheet for every month.

thank in advance
PS.
Sorry on my broken english


----------



## ekrause (Nov 18, 2022)

I don't know xlookup that well so someone else might be able to jump in with a newer Excel formula to solve for this. You want to use an Index(Match(),Match()) function. I think I nailed the formula from your picture but you may need to adjust some of the ranges.

=INDEX($A$2:$E$301,MATCH($K3,$A$2:$A$301,0),MATCH(N$1,$A$2:$E$2,0))

The formula above is looking to match both the Item Number and the Date and give you the intersection of the data. 

mfg.


----------



## forza fiume (Nov 18, 2022)

Dear Ekrause,

Thx, i ll try


----------



## forza fiume (Dec 7, 2022)

forza fiume said:


> Dear Ekrause,
> 
> Thx, i ll try


Sorry Ekrause, dont work


----------



## ekrause (Dec 7, 2022)

Did you find a good solution or are you still looking for help? What errors does this bring up?


----------



## forza fiume (Dec 13, 2022)

Thx for asking, I still looking for solution
Proba Sissy1.xlsxD100Prodaja

or
12/1/2212/2/22DAILY REPORT forDATUM12/1/22Produkt IDName Einzelpreis € UNITUNITRevenueProdukt IDName Einzelpreis € Unit SOLDRevenue1000Aura Teranino (Rotweinlikör) 0,7L25.00 €010011001Aura Oliven und Mandeln 0,7L (Maslina I Badem)€ 25.00100€ 2,500.001001Aura Oliven und Mandeln 0,7L (Maslina I Badem)25.00 €10011250021003Aura Wildbirne 0,7L (Divlja kruška)€ 25.00101€ 2,525.001002Aura Wilder Apfel 0,7L (Divlja jabuka)25.00 €012031011Aura Feigen 0,7 L (Smokva)€ 25.0022€ 550.001003Aura Wildbirne 0,7L (Divlja kruška)25.00 €10112252541012Aura Teranino (Rotweinlikör) 0,2L€ 13.0015€ 195.001004Aura Kräuterbrand 0,7L (Travarica)25.00 €0051014Aura Wilder Apfel 0,2L€ 13.005€ 65.001005Aura Salbei 0,7L (Kadulja)25.00 €0061016Aura Kräuterbrand 0,2L€ 13.002€ 26.001006Aura Kornelkirsche 0,7L (Drijen)25.00 €0071017Aura Salbei 0,2L€ 13.003€ 39.001007Aura Grappa Muscato 0,7L25.00 €03081019Aura Grappa Muscato 0,2L€ 13.002€ 26.001008Aura Divlje Voce (Waldbeeren) 0,7 L25.00 €0091021Aura Grappa Bianca 0,2 L € 13.005€ 65.001009Aura Biska 0,7 l25.00 €00104049Schafskäse mit Trüffeln 220g€ 16.001€ 16.001010Aura Grappa Bianca 0,7 L 25.00 €00115009BIOBAZA BODY LOTION PURPLE FRESHNESS  lavanda & limun€ 7.001€ 7.001011Aura Feigen 0,7 L (Smokva)25.00 €2255012  0#VALUE!1012Aura Teranino (Rotweinlikör) 0,2L13.00 €1519513  0#VALUE!1013Aura Oliven und Mandeln 0,2L13.00 €014  0#VALUE!1014Aura Wilder Apfel 0,2L13.00 €56515  0#VALUE!1015Aura Wildbirne 0,2L13.00 €016  0#VALUE!1016Aura Kräuterbrand 0,2L13.00 €22617  0#VALUE!1017Aura Salbei 0,2L13.00 €33918  0#VALUE!1018Aura Kornkirsche 0,2L13.00 €019  0#VALUE!1019Aura Grappa Muscato 0,2L13.00 €22620  0#VALUE!1020Aura Feigen 0,2 L (Smokva)13.00 €021  0#VALUE!1021Aura Grappa Bianca 0,2 L 13.00 €56522  0#VALUE!1022Aura Biska 0,2 l13.00 €023  0#VALUE!1023Aura Pflaumenschnaps 0,2 l13.00 €024  0#VALUE!1024Aura Honigschnaps 0,2 l13.00 €025  0#VALUE!1025Aura Divlje Voce (Waldbeeren) 0,2 L13.00 €026  0#VALUE!1026Aura Teranino (Rotweinlikör) 0,1L8.00 €027  0#VALUE!1027Aura Oliven und Mandeln 0,1L8.00 €028  0#VALUE!1028Aura Wilder Apfel 0,1L8.00 €029TOTALE€ 257.00€ 6,014.001029Aura Wildbirne 0,1L8.00 €01030Aura Kräuterbrand 0,1L8.00 €01031Aura Salbei 0,1L8.00 €01032Aura Kornkirsche 0,1L8.00 €01033Aura Grappa Muscato 0,1L8.00 €01034Aura Divlje Voce (Waldbeeren) 0,1 L8.00 €01035Aura Feigen 0,1 L8.00 €01036Aura Grappa Bianca 0,1 L8.00 €01037Aura Feigenkonfiture8.00 €01038Aura Pflaumenkonfiture8.00 €01039Aura Brombeerkonfiture8.00 €01040Aura Himbeerkonfiture8.00 €0


----------



## forza fiume (Dec 13, 2022)

Dear Ekrause 
can you see formula in this mini-sheet
Thx a lot


----------



## ekrause (Dec 13, 2022)

=INDEX($A$2:$E$301,MATCH($K3,$A$2:$A$301,0),MATCH(N$1,$A$1:$E$1,0))

Try this.


----------



## forza fiume (Dec 16, 2022)

Thx, good tip. For column N; N3:N28 I make this N3=INDEX(D3:F251;XMATCH(K3;A3:A251);XMATCH($N$1;$D$2:$F$2)),
if I choose in cell N1 -01.12.2022 work good for column "D" for column E (2.12.2022)  and etc list formula  keep product ID and name of 1.12.22 even value in D column is ”0”.
So, column N3:N28 should copy value from column D or E depending on the date I choose in N1. In daily report, value can not be a ZERO or empty


----------



## forza fiume (Dec 16, 2022)

DAILY REPORT forDATUM02/12/22Produkt IDName Einzelpreis € 12/1/2212/2/2212/3/22Produkt IDName Einzelpreis € Unit SOLDRevenue1000Aura Teranino (Rotweinlikör) 0,7L25.00 $1311000Aura Teranino (Rotweinlikör) 0,7L€ 25.003€ 75.001001Aura Oliven und Mandeln 0,7L (Maslina I Badem)25.00 $2021001Aura Oliven und Mandeln 0,7L (Maslina I Badem)€ 25.000€ 0.001002Aura Wilder Apfel 0,7L (Divlja jabuka)25.00 $3531002Aura Wilder Apfel 0,7L (Divlja jabuka)€ 25.005€ 125.001003Aura Wildbirne 0,7L (Divlja kruška)25.00 $441003Aura Wildbirne 0,7L (Divlja kruška)€ 25.000€ 0.001004Aura Kräuterbrand 0,7L (Travarica)25.00 $551004Aura Kräuterbrand 0,7L (Travarica)€ 25.000€ 0.001005Aura Salbei 0,7L (Kadulja)25.00 $1061005Aura Salbei 0,7L (Kadulja)€ 25.000€ 0.001006Aura Kornelkirsche 0,7L (Drijen)25.00 $77  #N/A#N/A1007Aura Grappa Muscato 0,7L25.00 $88  #N/A#N/A1008Aura Divlje Voce (Waldbeeren) 0,7 L25.00 $99  #N/A#N/A1009Aura Biska 0,7 l25.00 $1010  #N/A#N/A1010Aura Grappa Bianca 0,7 L 25.00 $1111  #N/A#N/A1011Aura Feigen 0,7 L (Smokva)25.00 $1212  #N/A#N/A1012Aura Teranino (Rotweinlikör) 0,2L13.00 $1313  #N/A#N/A1013Aura Oliven und Mandeln 0,2L13.00 $14  #N/A#N/A1014Aura Wilder Apfel 0,2L13.00 $3315  #N/A#N/A1015Aura Wildbirne 0,2L13.00 $16  #N/A#N/A1016Aura Kräuterbrand 0,2L13.00 $17  #N/A#N/A1017Aura Salbei 0,2L13.00 $518  #N/A#N/A1018Aura Kornkirsche 0,2L13.00 $19  #N/A#N/A1019Aura Grappa Muscato 0,2L13.00 $20  #N/A#N/A1020Aura Feigen 0,2 L (Smokva)13.00 $21  #N/A#N/A1021Aura Grappa Bianca 0,2 L 13.00 $22  #N/A#N/A1022Aura Biska 0,2 l13.00 $23  #N/A#N/A1023Aura Pflaumenschnaps 0,2 l13.00 $24  #N/A#N/A1024Aura Honigschnaps 0,2 l13.00 $25  #N/A#N/A1025Aura Divlje Voce (Waldbeeren) 0,2 L13.00 $26  #N/A#N/A1026Aura Teranino (Rotweinlikör) 0,1L8.00 $27  #N/A#N/A1027Aura Oliven und Mandeln 0,1L8.00 $28TOTALE€ 8.00€ 200.001028Aura Wilder Apfel 0,1L8.00 $29


----------



## forza fiume (Nov 18, 2022)

Dear Mr Excel,
of course, I have a problem.
In the hotel where I work, we have to make a daily report of sold souvenirs and other items. I solved columns K,L and M but I have a problem with column N. 
For now in cell N3 formula is =XLOOKUP(K3;$A$3:$A$301;$D$3:$D$301;0; 0) and that is fine for one day. 
My wish is that column N show value depending on the date chosen in N1( drop-down list). Which formula can choose D, E, F ...etc. for every day in a month

Daily report should be saved like a PDF to send to the bookkeeper.
Excel file must have a sheet for every month.

thank in advance
PS.
Sorry on my broken english


----------



## forza fiume (Dec 16, 2022)

Obviously, I do something wrong.
Solution , sort with DATA-Filter, copy Paste


----------

