Excel, filling in cell values automatically

Solved/Closed
as79 - Aug 16, 2010 at 03:51 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Aug 16, 2010 at 11:02 PM
Hello,

I have two worksheets in Excel 2007 and am trying to automatically fill in cells in worksheet two based on values in the first worksheet. Any assistance in code would be greatly appreciated!

Both worksheets contain name, lastname, address, phone of people (no unique identifiers though). Some of these people's details are repeated in worksheet2. Whenever an identical name (first name, last name) is typed into the second worksheet, I would like the contact details fields in worksheet 2(address, phone number, etc) to be filled automatically.

Could anyone help with the code?

Cheers

2 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Aug 16, 2010 at 06:31 AM
I give you small sample data. try this on sample data and you can modify

sheet 1 contains data from A1 to D5


first name last name address phone no.
a s sssss 123
a d ddddd 234
s d ffffffff 345
f g gggggg 678


sheet 2 contains data from A1 to B3


first name last name
a d
f g


in the second sheet in C2 copy this formula
=INDEX(Sheet1!C$2:C$5,MATCH(1,((Sheet1!$A$2:$A$5=A2)*(Sheet1!$B$2:$B$5=B2)),1))

INVOKE THIS FORMULA WITH CONTROL+SHIFT+ENTER

copy C2 down and to right.

you get the results like this


first name last name address phoneno.
a d ddddd 678
f g gggggg 678


Hope you can modify to suit you.
1
Hi, thanks for your help. Not sure what I'm doing wrong... I tried this but it came up with #N/A error, so I tried to use exactly the data and worksheets that you described in your example, but the code still didn't work for me. When I show the Calculation Steps to try and see what the prob is, it has:
=INDEX(Sheet1!C$2:C$5,MATCH(1, 0, 1))
0
please disregard my last comment!! I got it working - I had forgotten to use your instruction to CTL SHIFT ENTER to invoke formula.

Thank you so much!!! :)
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Aug 16, 2010 at 11:02 PM
you are welcome.
0