Index Match Query returning incorrect value

[trish123]

=IFERROR(VLOOKUP(B18,Data!$A$1:$U$289,MATCH($C$16,Data!$A$1:$U$1,1)),"")

The above is the query - the issue is that it is returning the incorrect information. The information that is being returned is being pulled is from another sheet where there is a data validation list, but somehow it is not returning what has been chosen, but another item on the list.

When i drag this query down to other cells it only sees the same item on the list?

Anyone any ideas ??

 

[AlanY]

Perfect AlanY thank you for all the help, by any chance could you help with the button to save the invoice to a tracker?

glad that we finally got that sorted.
not quite sure of your question re button to save the invoice, are you looking for a macro to export the invoice?

anyway, i'll logging off for the day. I'll have a look tomorrow if you will elaborate a bit further of the requirement.

 

[trish123]

You truly are a godsend yes currently i have a button for allow user to print the invoice which works fine, but i also want a button to allow the user to save the information input into the invoice to the tracker. Once the user clicks the button the information is then saved to tracker and the information will clear from the invoice to allow the user to create another invoice;

Permissions 2.xlsm
	A	B	C	D	E	F	G	H
1	Invoice Number	Name	Address	Event Number	Event Details	Date	Facility Fee	Total Amount
2								0
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Invoice Tracker

 

[AlanY]

ok. I've a invoice has 5 events (info taken from the data sheet)

	Invoice
	Customer Information				Invoice Information
	Name	A N Other			Date	23/09/2020
	Address	Somewhere1			Invoice Number	2020-1
		Somewhere2			Payment Required by Date	22/11/2020
		Somewhere3
		Somewhere4
	Site(s)
	Event Number	Event being requested	Facility Fee	Vat %	VAT amount	Total
	E-1	Filming - Educational	1000	20	200	1200
	E-2	Filming - Film	1200	20	240	1440
	E-3	Filming - Film	1440	20	288	1728
	E-4	Filming - Film	1728	20	345.6	2073.6
	E-5	Filming - Film	2073.6	20	414.72	2488.32

Staff Member Assigned	Event Number	Depot	Site	IF NATIONAL MONUMENT IS NOT LISTED - Only	Date Request Received	Event Organisers Details	Event being requested	Event Details - If dates are not known enter details here	Start Date of Event	End Date of Event
Breda	E-1	Trim				Joe Wicks	Filming - Educational	Filming	03/07/2020	03/07/2020
Breda	E-2	Trim				John Doe	Filming - Film	Choir Performance	17/07/2020	21/07/2020
Breda	E-3	Trim				John Doe	Filming - Film	Choir Performance	17/07/2020	21/07/2020
Breda	E-4	Trim				John Doe	Filming - Film	Choir Performance	17/07/2020	21/07/2020
Breda	E-5	Trim				John Doe	Filming - Film	Choir Performance	17/07/2020	21/07/2020

and to save it to the tracker like this, am I in the right direction?

Invoice Number	Name	Address	Event Number	Event Details	Date	Facility Fee	Total Amount
2020-1	A N Other	Somewhere1, Somewhere2, Somewhere3, Somewhere4	E-1	Filming	23/09/2020	1000	1200
			E-2	Choir Performance	23/09/2020	1200	1440
			E-3	Choir Performance	23/09/2020	1440	1728
			E-4	Choir Performance	23/09/2020	1728	2073.6
			E-5	Choir Performance	23/09/2020	2073.6	2488.32

 

[trish123]

Yes you are spot on

 

[AlanY]

ok, sorry about the late response.
been snowed down at work last few days.

this is the macro I came up with, based on the layout of your excel sheets

Sub UpdateTracker()
Dim FirstEvent As Integer
Dim new_row As Integer
Dim Events As Integer
'
Application.ScreenUpdating = False
    If MsgBox("Run the update?", vbYesNo) = vbNo Then Exit Sub
    If IsEmpty(Sheets("Invoice").Range("B17").Value) = True Then Exit Sub
'
Sheets("Invoice Tracker").Rows("2:2").Hidden = False
new_row = Sheets("Invoice Tracker").Cells(Rows.Count, 4).End(xlUp).Row
Events = Sheets("Invoice").Cells(Rows.Count, 2).End(xlUp).Row - 16
'
    Sheets("Invoice Tracker").Range("B2:H2").Copy _
        Sheets("Invoice Tracker").Range("B" & new_row + 1 & ":H" & new_row + Events)
    Sheets("Invoice").Range("B17:B" & Events + 16).Copy
        Sheets("Invoice Tracker").Range("D" & new_row + 1).PasteSpecial Paste:=xlPasteValues
    Sheets("Invoice Tracker").Range("B" & new_row + 2 & ":C" & new_row + 1 + Events).Clear
    Sheets("Invoice Tracker").Range("B3:H" & new_row + Events).Value = _
        Sheets("Invoice Tracker").Range("B3:H" & new_row + Events).Value
    Sheets("Invoice Tracker").Range("A" & new_row + 1).Value = _
        Sheets("Invoice").Range("G8").Value
    Sheets("Invoice").Range("B17:B100,D17:E100").ClearContents
Sheets("Invoice Tracker").Rows("2:2").Hidden = True
'
Application.ScreenUpdating = True
End Sub

 

[AlanY]

this is the invoice sheet with the data entered.
note that I'd change G8 for the invoice No as the original formula based on the tracker sheet thus caused conflict

Book1.xlsm
	A	B	C	D	E	F	G
1
2
3		Invoice
4
5		Customer Information				Invoice Information
6
7		Name	A N Other			Date	24/09/2020
8		Address	Somewhere1			Invoice Number	2020-1
9			Somewhere2			Payment Required by Date	23/11/2020
10			Somewhere3
11			Somewhere4
12		Site(s)
13
14
15
16		Event Number	Event being requested	Facility Fee	Vat %	VAT amount	Total
17		E-1	Filming - Film 1	1000	20	200	1200
18		E-2	Filming - Film 2	1200	20	240	1440
19		E-3	Filming - Film 3	1400	20	280	1680
20
21
22
23
24
25
26
Invoice

Cell Formulas
Range	Formula
G7	G7	=TODAY()
G8	G8	=YEAR(TODAY())&"-"&COUNTA('Invoice Tracker'!A:A)
G9	G9	=G7+60
B16	B16	=Data!B1
F17:F26	F17	=IF(E17="","",D17*E17%)
G17:G26	G17	=IF(E17="","",D17+F17)
C16	C16	=Data!H1
C17:C26	C17	=IF(B17="","",INDEX(Data!$A:$U,MATCH(Invoice!B17,Data!$B:$B,0),MATCH(Invoice!C$16,Data!$1:$1,0)))

 

[AlanY]

this is the tracker sheet, before and after the update tracker macro
note that the Row 2 with formula is hidden after first run

Book1.xlsm
	A	B	C	D	E	F	G	H
1	Invoice Number	Name	Address	Event Number	Event Details	Date	Facility Fee	Total
2		A N Other	Somewhere1, Somewhere2, Somewhere3, Somewhere4	E-1	Choir Performance 1	24/09/2020	1000	1200
3
4
5
6
7
8
Invoice Tracker

Cell Formulas
Range	Formula
B2	B2	=Invoice!$C$7
C2	C2	=TEXTJOIN(", ",,Invoice!$C$8:$C$11)
E2	E2	=INDEX(Data!$A:$M,MATCH('Invoice Tracker'!$D2,Data!$B:$B,0),9)
F2	F2	=Invoice!$G$7
G2:H2	G2	=INDEX(Invoice!$B$16:$G$28,MATCH('Invoice Tracker'!$D2,Invoice!$B$16:$B$28,0),MATCH('Invoice Tracker'!G$1,Invoice!$B$16:$G$16,0))

Book1.xlsm
	A	B	C	D	E	F	G	H
1	Invoice Number	Name	Address	Event Number	Event Details	Date	Facility Fee	Total
3	2020-1	A N Other	Somewhere1, Somewhere2, Somewhere3, Somewhere4	E-1	Choir Performance 1	24/09/2020	1000	1200
4				E-2	Choir Performance 2	24/09/2020	1200	1440
5				E-3	Choir Performance 3	24/09/2020	1400	1680
6
Invoice Tracker

 

[AlanY]

after the tracker updated, the events data will be clear and invoice no increase by 1

Book1.xlsm
	A	B	C	D	E	F	G
1
2
3		Invoice
4
5		Customer Information				Invoice Information
6
7		Name	A N Other			Date	24/09/2020
8		Address	Somewhere1			Invoice Number	2020-2
9			Somewhere2			Payment Required by Date	23/11/2020
10			Somewhere3
11			Somewhere4
12		Site(s)
13
14
15
16		Event Number	Event being requested	Facility Fee	Vat %	VAT amount	Total
17
18
19
20
21
22
23
24
25
26
Invoice

Cell Formulas
Range	Formula
G7	G7	=TODAY()
G8	G8	=YEAR(TODAY())&"-"&COUNTA('Invoice Tracker'!A:A)
G9	G9	=G7+60
B16	B16	=Data!B1
F17:F26	F17	=IF(E17="","",D17*E17%)
G17:G26	G17	=IF(E17="","",D17+F17)
C16	C16	=Data!H1
C17:C26	C17	=IF(B17="","",INDEX(Data!$A:$U,MATCH(Invoice!B17,Data!$B:$B,0),MATCH(Invoice!C$16,Data!$1:$1,0)))

 

[AlanY]

the second invoice as an example

Book1.xlsm
	A	B	C	D	E	F	G
1
2
3		Invoice
4
5		Customer Information				Invoice Information
6
7		Name	A N Other			Date	24/09/2020
8		Address	Somewhere1			Invoice Number	2020-2
9			Somewhere2			Payment Required by Date	23/11/2020
10			Somewhere3
11			Somewhere4
12		Site(s)
13
14
15
16		Event Number	Event being requested	Facility Fee	Vat %	VAT amount	Total
17		E-4	Filming - Film 4	2000	20	400	2400
18		E-5	Filming - Film 5	2200	20	440	2640
19		E-6	Filming - Film 6	2001	20	400.2	2401.2
20		E-7	Filming - Film 7	2201	20	440.2	2641.2
21		E-8	Filming - Film 8	2002	20	400.4	2402.4
22
23
24
25
26
Invoice

Cell Formulas
Range	Formula
G7	G7	=TODAY()
G8	G8	=YEAR(TODAY())&"-"&COUNTA('Invoice Tracker'!A:A)
G9	G9	=G7+60
B16	B16	=Data!B1
F17:F26	F17	=IF(E17="","",D17*E17%)
G17:G26	G17	=IF(E17="","",D17+F17)
C16	C16	=Data!H1
C17:C26	C17	=IF(B17="","",INDEX(Data!$A:$U,MATCH(Invoice!B17,Data!$B:$B,0),MATCH(Invoice!C$16,Data!$1:$1,0)))

Book1.xlsm
	A	B	C	D	E	F	G	H
1	Invoice Number	Name	Address	Event Number	Event Details	Date	Facility Fee	Total
3	2020-1	A N Other	Somewhere1, Somewhere2, Somewhere3, Somewhere4	E-1	Choir Performance 1	24/09/2020	1000	1200
4				E-2	Choir Performance 2	24/09/2020	1200	1440
5				E-3	Choir Performance 3	24/09/2020	1400	1680
6	2020-2	A N Other	Somewhere1, Somewhere2, Somewhere3, Somewhere4	E-4	Choir Performance 4	24/09/2020	2000	2400
7				E-5	Choir Performance 5	24/09/2020	2200	2640
8				E-6	Choir Performance 6	24/09/2020	2001	2401.2
9				E-7	Choir Performance 7	24/09/2020	2201	2641.2
10				E-8	Choir Performance 8	24/09/2020	2002	2402.4
Invoice Tracker

Book1.xlsm
	A	B	C	D	E	F	G
1
2
3		Invoice
4
5		Customer Information				Invoice Information
6
7		Name	A N Other			Date	24/09/2020
8		Address	Somewhere1			Invoice Number	2020-3
9			Somewhere2			Payment Required by Date	23/11/2020
10			Somewhere3
11			Somewhere4
12		Site(s)
13
14
15
16		Event Number	Event being requested	Facility Fee	Vat %	VAT amount	Total
17
18
19
20
21
22
23
24
25
26
Invoice

Cell Formulas
Range	Formula
G7	G7	=TODAY()
G8	G8	=YEAR(TODAY())&"-"&COUNTA('Invoice Tracker'!A:A)
G9	G9	=G7+60
B16	B16	=Data!B1
F17:F26	F17	=IF(E17="","",D17*E17%)
G17:G26	G17	=IF(E17="","",D17+F17)
C16	C16	=Data!H1
C17:C26	C17	=IF(B17="","",INDEX(Data!$A:$U,MATCH(Invoice!B17,Data!$B:$B,0),MATCH(Invoice!C$16,Data!$1:$1,0)))

 

[AlanY]

I've uploaded the file here

Book1.xlsm

drive.google.com