Quick excel question

Cyrus

Cyrus

Cyrus

Soldato
Joined
18 Oct 2002
Posts
3,669
Location
Bubblin in Dublin
this is probably very basic but i cant figure it. i have an excel work book with two work sheets.

Work sheet one is a list of purchase orders with a reference number beginning PO and then a 5 digit no

Sheet 2 is a list of invoices and one column is reserved to show the purchase order the invoice was raised on.

What i want to do is, in sheet one have a column that shows 1 if the purchase order number appears in the column on the next sheet or 0 if it doesnt.

basically what i want to do is get excel to try match the number in a given cell to the same number in a column on the next work sheet.

any ideas?
 
I don't know if this is the open office format of if it also works in excel but:

$Sheet2.C3 refers to C3 on sheet 2 (duh). You could try using that sort of notation in your own work sheet and see if it works out?

You can also do $Sheet2.$C3 (I think that means if you drag the formula it always refers to the same col etc.
 
prob is to match say c1 on sheet 1 to a column on sheet 2 it will have to search the whole column as they wont be in same order
 
Use INDEX with MATCH to locate and return the invoice on Sheet2 relating to the purchase order on Sheet1.

Syntax = INDEX (target list, MATCH (source, source list, 0))

Example:

Sheet1:
Column A1:A20 = List of 20 PO's

Sheet 2
Column A1:A20 = List of invoice Numbers
Column B1:B20 = List of PO's

In Sheet1 column B enter the following formula and copy down:

=INDEX(Sheet2!A$1:A$20,MATCH(A1,Sheet2!B$1:B$20,0))

This will attempt to retrieve an invoice number from Sheet2 Column A, where the PO in Column B matches the PO in Sheet1 A1. If no match is found it will return #N/A.

Then if you wish to not replace the retreived invoice number with a 1 for found or a 0 for not found, change to the following formula:

=IF(ISERROR(INDEX(Sheet2!A$1:A$20,MATCH(A1,Sheet2!B$1:B$20,0))),"0","1")

Here you are adding the ISERROR check to the formula. If #N/A is returned then the value will be 0, else 1.

I have attached an example for you to see. Cyrus.xls

Hope this helps :)
 
You must log in or register to reply here.
Back
Top Bottom