T
txheart
Good morning,
I posted this question initially at the Mr. Excel forums, but I haven'
received a response, so decided to try here. I am not an advanced user
though I used to think I was - lol.
I've got 2 workbooks: one is a listing of our entire company'
employee's insurance information; the other is a listing of just thi
office's demographical information. My goal is to have the entir
company database, let's call it Global, look at the Office database
match the names, and if a match exists, to enter the employee's I
number that is in the Office spreadsheet to a cell in the Globa
spreadsheet.
The Global spreadsheet will not match all the names, and they won't b
in the same order - not even close. I would like the Global to chec
the other spreadsheet if, and only if, column C says Mississippi. I
Global sees an employee based in Mississippi, then it should check th
Office database to see if a match exists - if a match exists, copy th
ID number from Office and put it into Global.
I messed with this all day long yesterday, and have come up with
=INDEX([Office.xls]Sheet1!M1:M1000,MATCH(1,([Office.xls]Sheet1!B1:B1000=A16)*([Office.xls]Sheet1!C1:C1000=B16),0))
That works, but there's a couple of issues with it.
The first is that this command is checking every name against the othe
spreadsheet, resulting in a ton of #N/A that I dont want there.
The second issue is that dragging the formula down changes all of th
cells to the next cell -- With say 280 rows in Global, this dragge
formula, the farther down I drag, the less of the other spreadshee
it's actually looking at. I don't want the M1, B1, C1 to change to M85
etc - I want every cell to look from row 1-1000. So, when I drag, I hav
to go every 5 cells or so and change all of the references, except th
last one, to 1 - the employee it's trying to match might just be #2 an
dragging that formula down would cause the formula to miss the employee
The final issue, that I've encountered so far, is that I would rathe
not have #N/A on the spreadsheet at all. If it's a Mississippi employe
but there is no name match then I'd like to see "Unknown" rather tha
#N/A because that cell is actually applicable, just not known at th
time.
Goodness, I'm wordy! I've tried to explain exactly what I'm trying t
accomplish, but if there are any questions, or if something need
clarified, I'll certainly try again. I look forward to y'all'
responses.
K
I posted this question initially at the Mr. Excel forums, but I haven'
received a response, so decided to try here. I am not an advanced user
though I used to think I was - lol.
I've got 2 workbooks: one is a listing of our entire company'
employee's insurance information; the other is a listing of just thi
office's demographical information. My goal is to have the entir
company database, let's call it Global, look at the Office database
match the names, and if a match exists, to enter the employee's I
number that is in the Office spreadsheet to a cell in the Globa
spreadsheet.
The Global spreadsheet will not match all the names, and they won't b
in the same order - not even close. I would like the Global to chec
the other spreadsheet if, and only if, column C says Mississippi. I
Global sees an employee based in Mississippi, then it should check th
Office database to see if a match exists - if a match exists, copy th
ID number from Office and put it into Global.
I messed with this all day long yesterday, and have come up with
=INDEX([Office.xls]Sheet1!M1:M1000,MATCH(1,([Office.xls]Sheet1!B1:B1000=A16)*([Office.xls]Sheet1!C1:C1000=B16),0))
That works, but there's a couple of issues with it.
The first is that this command is checking every name against the othe
spreadsheet, resulting in a ton of #N/A that I dont want there.
The second issue is that dragging the formula down changes all of th
cells to the next cell -- With say 280 rows in Global, this dragge
formula, the farther down I drag, the less of the other spreadshee
it's actually looking at. I don't want the M1, B1, C1 to change to M85
etc - I want every cell to look from row 1-1000. So, when I drag, I hav
to go every 5 cells or so and change all of the references, except th
last one, to 1 - the employee it's trying to match might just be #2 an
dragging that formula down would cause the formula to miss the employee
The final issue, that I've encountered so far, is that I would rathe
not have #N/A on the spreadsheet at all. If it's a Mississippi employe
but there is no name match then I'd like to see "Unknown" rather tha
#N/A because that cell is actually applicable, just not known at th
time.
Goodness, I'm wordy! I've tried to explain exactly what I'm trying t
accomplish, but if there are any questions, or if something need
clarified, I'll certainly try again. I look forward to y'all'
responses.
K