Revenue report depend on date

[forza fiume]

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]

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]

Dear Ekrause,

Thx, i ll try

 

[forza fiume]

Dear Ekrause,

Thx, i ll try

Sorry Ekrause, dont work

 

[ekrause]

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

 

[forza fiume]

Thx for asking, I still looking for solution

Proba Sissy1.xlsx
	D
10	0
Prodaja

or

			12/1/22	12/2/22							DAILY REPORT for	DATUM	12/1/22
Produkt ID	Name	Einzelpreis €	UNIT	UNIT	Revenue					Produkt ID	Name	Einzelpreis €	Unit SOLD	Revenue
1000	Aura Teranino (Rotweinlikör) 0,7L	25.00 €	0	10	0				1	1001	Aura Oliven und Mandeln 0,7L (Maslina I Badem)	€ 25.00	100	€ 2,500.00
1001	Aura Oliven und Mandeln 0,7L (Maslina I Badem)	25.00 €	100	11	2500				2	1003	Aura Wildbirne 0,7L (Divlja kruška)	€ 25.00	101	€ 2,525.00
1002	Aura Wilder Apfel 0,7L (Divlja jabuka)	25.00 €	0	12	0				3	1011	Aura Feigen 0,7 L (Smokva)	€ 25.00	22	€ 550.00
1003	Aura Wildbirne 0,7L (Divlja kruška)	25.00 €	101	12	2525				4	1012	Aura Teranino (Rotweinlikör) 0,2L	€ 13.00	15	€ 195.00
1004	Aura Kräuterbrand 0,7L (Travarica)	25.00 €	0		0				5	1014	Aura Wilder Apfel 0,2L	€ 13.00	5	€ 65.00
1005	Aura Salbei 0,7L (Kadulja)	25.00 €	0		0				6	1016	Aura Kräuterbrand 0,2L	€ 13.00	2	€ 26.00
1006	Aura Kornelkirsche 0,7L (Drijen)	25.00 €	0		0				7	1017	Aura Salbei 0,2L	€ 13.00	3	€ 39.00
1007	Aura Grappa Muscato 0,7L	25.00 €	0	3	0				8	1019	Aura Grappa Muscato 0,2L	€ 13.00	2	€ 26.00
1008	Aura Divlje Voce (Waldbeeren) 0,7 L	25.00 €	0		0				9	1021	Aura Grappa Bianca 0,2 L	€ 13.00	5	€ 65.00
1009	Aura Biska 0,7 l	25.00 €	0		0				10	4049	Schafskäse mit Trüffeln 220g	€ 16.00	1	€ 16.00
1010	Aura Grappa Bianca 0,7 L	25.00 €	0		0				11	5009	BIOBAZA BODY LOTION PURPLE FRESHNESS lavanda & limun	€ 7.00	1	€ 7.00
1011	Aura Feigen 0,7 L (Smokva)	25.00 €	22		550				12				0	#VALUE!
1012	Aura Teranino (Rotweinlikör) 0,2L	13.00 €	15		195				13				0	#VALUE!
1013	Aura Oliven und Mandeln 0,2L	13.00 €			0				14				0	#VALUE!
1014	Aura Wilder Apfel 0,2L	13.00 €	5		65				15				0	#VALUE!
1015	Aura Wildbirne 0,2L	13.00 €			0				16				0	#VALUE!
1016	Aura Kräuterbrand 0,2L	13.00 €	2		26				17				0	#VALUE!
1017	Aura Salbei 0,2L	13.00 €	3		39				18				0	#VALUE!
1018	Aura Kornkirsche 0,2L	13.00 €			0				19				0	#VALUE!
1019	Aura Grappa Muscato 0,2L	13.00 €	2		26				20				0	#VALUE!
1020	Aura Feigen 0,2 L (Smokva)	13.00 €			0				21				0	#VALUE!
1021	Aura Grappa Bianca 0,2 L	13.00 €	5		65				22				0	#VALUE!
1022	Aura Biska 0,2 l	13.00 €			0				23				0	#VALUE!
1023	Aura Pflaumenschnaps 0,2 l	13.00 €			0				24				0	#VALUE!
1024	Aura Honigschnaps 0,2 l	13.00 €			0				25				0	#VALUE!
1025	Aura Divlje Voce (Waldbeeren) 0,2 L	13.00 €			0				26				0	#VALUE!
1026	Aura Teranino (Rotweinlikör) 0,1L	8.00 €			0				27				0	#VALUE!
1027	Aura Oliven und Mandeln 0,1L	8.00 €			0				28				0	#VALUE!
1028	Aura Wilder Apfel 0,1L	8.00 €			0				29			TOTALE	€ 257.00	€ 6,014.00
1029	Aura Wildbirne 0,1L	8.00 €			0
1030	Aura Kräuterbrand 0,1L	8.00 €			0
1031	Aura Salbei 0,1L	8.00 €			0
1032	Aura Kornkirsche 0,1L	8.00 €			0
1033	Aura Grappa Muscato 0,1L	8.00 €			0
1034	Aura Divlje Voce (Waldbeeren) 0,1 L	8.00 €			0
1035	Aura Feigen 0,1 L	8.00 €			0
1036	Aura Grappa Bianca 0,1 L	8.00 €			0
1037	Aura Feigenkonfiture	8.00 €			0
1038	Aura Pflaumenkonfiture	8.00 €			0
1039	Aura Brombeerkonfiture	8.00 €			0
1040	Aura Himbeerkonfiture	8.00 €			0

 

[forza fiume]

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

 

[ekrause]

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

Try this.

 

[forza fiume]

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]

											DAILY REPORT for	DATUM	02/12/22
Produkt ID	Name	Einzelpreis €	12/1/22	12/2/22	12/3/22					Produkt ID	Name	Einzelpreis €	Unit SOLD	Revenue
1000	Aura Teranino (Rotweinlikör) 0,7L	25.00 $	1	3					1	1000	Aura Teranino (Rotweinlikör) 0,7L	€ 25.00	3	€ 75.00
1001	Aura Oliven und Mandeln 0,7L (Maslina I Badem)	25.00 $	2	0					2	1001	Aura Oliven und Mandeln 0,7L (Maslina I Badem)	€ 25.00	0	€ 0.00
1002	Aura Wilder Apfel 0,7L (Divlja jabuka)	25.00 $	3	5					3	1002	Aura Wilder Apfel 0,7L (Divlja jabuka)	€ 25.00	5	€ 125.00
1003	Aura Wildbirne 0,7L (Divlja kruška)	25.00 $	4						4	1003	Aura Wildbirne 0,7L (Divlja kruška)	€ 25.00	0	€ 0.00
1004	Aura Kräuterbrand 0,7L (Travarica)	25.00 $	5						5	1004	Aura Kräuterbrand 0,7L (Travarica)	€ 25.00	0	€ 0.00
1005	Aura Salbei 0,7L (Kadulja)	25.00 $	1	0					6	1005	Aura Salbei 0,7L (Kadulja)	€ 25.00	0	€ 0.00
1006	Aura Kornelkirsche 0,7L (Drijen)	25.00 $		7					7				#N/A	#N/A
1007	Aura Grappa Muscato 0,7L	25.00 $		8					8				#N/A	#N/A
1008	Aura Divlje Voce (Waldbeeren) 0,7 L	25.00 $		9					9				#N/A	#N/A
1009	Aura Biska 0,7 l	25.00 $			10				10				#N/A	#N/A
1010	Aura Grappa Bianca 0,7 L	25.00 $			11				11				#N/A	#N/A
1011	Aura Feigen 0,7 L (Smokva)	25.00 $			12				12				#N/A	#N/A
1012	Aura Teranino (Rotweinlikör) 0,2L	13.00 $			13				13				#N/A	#N/A
1013	Aura Oliven und Mandeln 0,2L	13.00 $							14				#N/A	#N/A
1014	Aura Wilder Apfel 0,2L	13.00 $			33				15				#N/A	#N/A
1015	Aura Wildbirne 0,2L	13.00 $							16				#N/A	#N/A
1016	Aura Kräuterbrand 0,2L	13.00 $							17				#N/A	#N/A
1017	Aura Salbei 0,2L	13.00 $			5				18				#N/A	#N/A
1018	Aura Kornkirsche 0,2L	13.00 $							19				#N/A	#N/A
1019	Aura Grappa Muscato 0,2L	13.00 $							20				#N/A	#N/A
1020	Aura Feigen 0,2 L (Smokva)	13.00 $							21				#N/A	#N/A
1021	Aura Grappa Bianca 0,2 L	13.00 $							22				#N/A	#N/A
1022	Aura Biska 0,2 l	13.00 $							23				#N/A	#N/A
1023	Aura Pflaumenschnaps 0,2 l	13.00 $							24				#N/A	#N/A
1024	Aura Honigschnaps 0,2 l	13.00 $							25				#N/A	#N/A
1025	Aura Divlje Voce (Waldbeeren) 0,2 L	13.00 $							26				#N/A	#N/A
1026	Aura Teranino (Rotweinlikör) 0,1L	8.00 $							27				#N/A	#N/A
1027	Aura Oliven und Mandeln 0,1L	8.00 $							28			TOTALE	€ 8.00	€ 200.00
1028	Aura Wilder Apfel 0,1L	8.00 $							29