Pre-filling data based on a cell

Hello,

I am trying to build a printable A5 birthday voucher (or at least exportable as PDF) for my clients. I have three data lists: their name, their DOB & their email address.

What I want to do is select from the drop-down list a customer's name, then the DOB be pre-filled in another sell automatically. Thus, the DOB cell must correspond, and automatically fill, based on the name selected from the drop-down list.

I apologise if this has been answered before, I've spent 45mins looking in the forum, and have learnt a lot, but I still don't have a solution to this problem. Any help would be appreciated.

Thank you.


Jake Leslie,

Say you have the name in cell A2, and want DOB and email to populate in B2 and C2.
You might have the data in a sheet called data with names in column A, DOB in B and email in C.
What you would do is VLOOKUP(A2,Data!A:C,2,FALSE) in B2 and VLOOKUP(A2,Data!A:C,3,FALSE) in C2

provided every customer name appears only once in the list

Thank you both for your responses...

That is now working perfectly for me:
=VLOOKUP(A5,'Birthday List'!B:D,3,FALSE)

However, I will certainly have duplications in names. Is there a way to assign a unique key to each row of data automatically?

Thanks !

Exactly. If I have two John Smiths, how will Excel know which DOB to bring up?

I didn't quite understand how =B5&COUNTIF($B$5:B5,B5) that works? However that's alright. I just entered =A2+1 where A2 is the above cell.

Now what I need to know is how to retrieve the correct DOB for John Smith (ID# 12) when there is also a John Smith (ID# 23).

Thanks a million.

Also, I have the DOB list now with two separate columns of information (1. day & month, 2. year), because I want the gift certificate to display the birthday as 'dddd d mmmm' without the year... However, when I have the field 'valid until' as the birthday cell + 4 weeks (validity of voucher), then it adds it from the current year, when I need it to be from the coming year. I.e. If we're in September and John Smith's birthday is on 24 August, it lists his birthday as Friday 24 August (i.e. from 2012), where I would like it do present the birthday from 2013. Is this possible?

G'day melvinrobb,

Here is the worksheet I am working from.

Thanks for your help mate.

G'day Melvinrobb,

I've got the spreadsheet working pretty well now. The only two issues (which are minor) are:

1. I have to perform a VLOOKUP [=VLOOKUP(A6,'Birthday List'!B:D,3,FALSE)] in a cell outside the worksheet, then establish the anniversary date using the EDATE function you suggested [=EDATE(G6,ROUNDUP(YEARFRAC(TODAY(),G6),0)*12)] within the worksheet printable area. So, this works, but it would be nice to have the full command in one cell.
2. I still don't have a solution to the duplication of entries, i.e. John Smith 1, 2 & 3 all with different birthdays. However, to be honest, this is not a problem at all, as it will take all of 5 seconds to sort through the list and find the correct client.

So, in conclusion... that's a win. Thanks for your help!

Chemist, I can't thank you enough. That works perfectly, and that was such a professional answer. Thank you very much.

@ beleza.jake

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.