Forum Home
Press F1
Thread ID: 38169	2003-09-29 10:24:00	Excel	nw3mrgc1 (3436)	Press F1

Post ID	Timestamp	Content	User
178985	2003-09-29 10:24:00	Hi, I'm using an excel file with 2 sheets. On Sheet2, there is a list named 'data'. On Sheet1 there are dropdown cells with source '=data'. I am looking for a formula in excel that I will put next to the data in Sheet2 that will give me "Used" if there is one or more occurences of this data in Sheet1. For example: Sheet2!a1 = a Sheet2!a2 = b Sheet2!a3 = c Sheet2!a4 = d Sheet2!a5 = e Name Sheet2!a1:a5 = "data" Select Sheet1!a1 => Data; Data Validation; Allow list; Source "=data" Select in dropdown list of Sheet1!a1 "d" Sheet2!b4 = "Used" <= Formula to give this result __________________________________________________ __ I used the following formula, but when there are no matches I get #N/A, which I don't want. =if(match(sheet2!a1,sheet1!a:a,0)>0,"Used","") Is there another formula? I don't want to use macros at this time if possible.	nw3mrgc1 (3436)
178986	2003-09-30 04:55:00	Hi, you can see here ( . cpearson . com/excel/duplicat . htm" target="_blank">www . cpearson . com) for ways of finding unique/duplicate values . You can also use your existing formula with an ISERROR function which tests to see if there is an error or not . The assumption being that if its not in error it must have found the value . A dangerous assumption if there are other errors such as a typo of a cell reference but an option none-the-less . eg =IF(ISERROR(MATCH(A1,B:B,0)>0)=FALSE,"Used","")	parry (27)
178987	2003-09-30 05:13:00	Can you not just use a simple if formula next to your data values on sheet 2 to compare the contents of sheet1 with sheet2? eg =IF(Sheet2!a1=Sheet1$a$1,"Used","")	odyssey (4613)
1