If I have a spreadsheet that I am adding an id number to, is there any way to check to see if that same ID number is already used in a different spreadsheet?
If the IDs in the existing file are in a single column, you can use VLOOKUP
- have both sheets open.
- let’s define that the “new” sheet is called New and has your ID in column A; the “old” sheet is called Old and has your ID in column Aleph; Old’s column Bet is full of the word EXISTS; both columns have headers; New’s column B is blank.
- In New, cell B2, write =vlookup(
and use the wizard to use New’s A2 as “what to check”, Old’s Aleph column as “what to check against”, Old’s Bet as “what to copy” and set the fourth value as “false” or “0” (same thing). Copy all the way down to the bottom of Column B.
That will look at the value in An, see if it finds it in Aleph
if found, will write EXISTS in Bn
if not found will write #N/A in Bn
And it doesn’t require either file to be ordered.
Is it always the same other spreadsheet? Cell references (as used, for instance, in a VLOOKUP() function) can point to cell ranges on other spreadsheet files, as long as the file is named.
What you can’t do with just a function is check all unnamed files in a directory, for instance, or some other kind of wildcard search.
So if you have an employee ID you’re putting into a payroll spreadsheet “payroll.xlsx” and you want to check if they’re a legitimate employee in Sheet 1 of “employee_roster.xlsx”, your cell reference would be something like '[employee_roster.xlsx]Sheet1!A1:A55) (color coding to indicate the parts I’m talking about)
The best way to do this is to have both spreadsheets open and, when it comes time to insert the other-sheet cell range into the formula, actually select the range in the other spreadsheet.
Thanks. I have a running list of numbers in a file called 2016Calls that gets sent one a week to another department. Each week another sheet is updated with the calls for that week. If someone calls on two different weeks I don’t want to add it to the 2016Calls sheet a second time but instead want a notice that it was already entered.
I will try the vlookup that gnoitall suggested
I’m lazy. I would just add the weekly calls to the bottom of a long list in 2016calls.
At this point you have duplicates.
There are two way to show only the unique values:
- Select the entire column and on the Data tab, in the Data Tools group, click Remove Duplicates.
- Create a pivot table. The advantage of this is that it updates easily, and can report the count of each number.