Hi guys,
I've tried fixing this problem by myself and have now given up.
Formula I am querying about is: =INDEX(INVOICE!1:1048576,MATCH(A1,INVOICE!A:A,0)+1,2)&""
On my main sheet, A1 is where I type the invoice number. My second sheet where I enter all the information is called INVOICE! and within A:A I enter all the invoices and B:B all the stationary description corresponding to the invoice. e.g. invoice 1 = pen, invoice 2 = pencil, invoice 2 = scissor, invoice 3 = pen. In this example, there will be 4 rows
On my main sheet, I have the above formula, and it returns the description by matching what invoice number I enter into A1. The first line will return the same row as the invoice number, the second line when moving the formula down will return the row below (adding +1 to formula per above) the column matching the number, the third will show 2 rows below (by adding +2), and so on.
My problem is, when I type in invoice number 1 on the main sheet, it will pick up the items on my invoice sheet i.e. invoice 1 = pen, pencil, scissor, pen. I only want invoice 1 to show invoice 1 stuff, not the other invoices. In addition, when I type invoice 2, it will pick up pencil, scissor, pen. However, I only want invoice 2 to show pencil and scissor.
My index / match formula needs an IF condition to select which data to extract, I don't know which one. Or, it needs another column to make the invoice number within the invoice sheet unique.
Please help.
I've tried fixing this problem by myself and have now given up.
Formula I am querying about is: =INDEX(INVOICE!1:1048576,MATCH(A1,INVOICE!A:A,0)+1,2)&""
On my main sheet, A1 is where I type the invoice number. My second sheet where I enter all the information is called INVOICE! and within A:A I enter all the invoices and B:B all the stationary description corresponding to the invoice. e.g. invoice 1 = pen, invoice 2 = pencil, invoice 2 = scissor, invoice 3 = pen. In this example, there will be 4 rows
On my main sheet, I have the above formula, and it returns the description by matching what invoice number I enter into A1. The first line will return the same row as the invoice number, the second line when moving the formula down will return the row below (adding +1 to formula per above) the column matching the number, the third will show 2 rows below (by adding +2), and so on.
My problem is, when I type in invoice number 1 on the main sheet, it will pick up the items on my invoice sheet i.e. invoice 1 = pen, pencil, scissor, pen. I only want invoice 1 to show invoice 1 stuff, not the other invoices. In addition, when I type invoice 2, it will pick up pencil, scissor, pen. However, I only want invoice 2 to show pencil and scissor.
My index / match formula needs an IF condition to select which data to extract, I don't know which one. Or, it needs another column to make the invoice number within the invoice sheet unique.
Please help.
