Automatic Cell Fill-in from Spreadsheet Database

[Guest]

I have a spreadsheet that has LastName/FirstName in cells A1 and B1
respectively. In C1 I want to put a formula that looks at another
spreadsheet set up as a database to pull the last 4 numbers of the person's
SS#. Since there could be more than one person with the same last name, the
formula would have to reference both cells A1 and B1 to make sure it got the
correct SS#. I am just not sure this is doable.

Thank you for any help!

 

[Biff]

Hi!

another spreadsheet set up as a database

Is that another separate file or another sheet in the same file?

How is the database setup?

Last name / First name in 2 cells?

How are the SS#'s entered? As the full xxx-xx-xxxx or just the last 4?

Many details to fill in!

Biff

 

[Guest]

Thanks Biff! The database is in the same workbook on a different worksheet.
The names (first and last) in the database are also in two separate cells.

The SS# on the database sheet is listed with just the last 4 numbers, so the
entire cell/block can be referenced.

I hope this helps. Thanks!!!

 

[Biff]

Ok.......

Assume the db is on Sheet2 and setup like thus:

A1:A100 = last n
B1:B100 = first n
C1:C100 = last 4 SS#

Sheet1? A1 = last n
Sheet1? B1 = first n

Sheet1? C1 = formula:

=SUMPRODUCT(--(Sheet2!A1:A100=A1),--(Sheet2!B1:B100=B1),Sheet2!C1:C100)

If it's possible that you may have dupes, like:

Smith | John | 1234
Smith | John | 5678

Then a different, more complicated approach will be needed.

Biff