Software

Ask something
Question Locked

Excel Formula Help

0 helpful
3 replies

by loftdog

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have an excel formula issue. I have a workbook with 14 worksheets. In sheet 1, I have UserId's in column A. In sheet2-sheet14 I have other UserId's in column A. I need to figure out how to notify sheet1 that the same UserId might be listed in one of the other sheets.

The column labels are for sheet 1: UserId, Name, Sheet2, Sheet3, 4, 5... under the column label (ex: Sheet3) I want it to display an X in a cell if the UserId exists anywhere in that sheet. I tried playing around with Match, Vlookup, Hlookup, and IF but I couldn't get anything to work right.

Any ideas? I am using Excel 2007.

3 replies

by thumbsup2

This works in Excel 2003. Should work in 2007. Note: the cells it's going to search in sheets 2-14 are actually columns A and B. Hopefully, you have no UserID in column B.

=IF(ISNA(VLOOKUP(A2,Sheet2!A$2:B$6,1,FALSE)=TRUE),"","X")

edit: Be sure to change the range reference. I only searched lines 2 through 6.

by gwhenning

Not sure if you're just trying to flag for duplicates, or if you need the 'X' in another column. If you're just trying to avoid duplicates, I'd use a conditional format. Highlight the row with the user IDs, choose conditional formatting in the ribbon, click New Rule, choose Use a Formula to Determine Which Cells to Format and enter: =IF(VLOOKUP(A1,Sheet2!$A:$A,1,FALSE)=A1,"TRUE","FALSE")

Make sure that the cell you want to compare is the first cell in your selection (in this case, I selected all of column A so 'A1' is the first, if you had selected 'A5:A29', 'A5' would be the first cell).

This discussion is closed to new replies.