Populate cells based on the selection from a drop down box

[bartow]

I am a platoon leader in the army, on a daily basis we have to fill out an excel form for the soldiers going with us out of base. I have one sheet that has my platoons information such as

Rank, Last Name/First Name, social, weapon number

I hate having to copy past the information over, especially when it can range from my platoon of 30 to the company of 130 some, I want to take the sheet we have to fill out and select the soldiers name from a drop down box and have it auto populate the rest of the soldiers data.

Thank you for your time

[JBeaucaire]

The formula to lookup the info is pretty simple, put this in A2 and copy down:

=IF($B2="","",INDEX(Sheet2!A:A,MATCH($B2,Sheet2!$B:$B,0)))

Also, then copy that column over to columns C and D as well. The formulas will update themselves as you copy, though you might have to play with the formatting.

But I thought I'd help with drop down, too. Open up the Named Range table (Insert > Name > Define in Excl 03, not sure where that is in Excel10).

I created a name range called NAMES using the RefersTo: formula:

=Sheet2!$B$1:INDEX(Sheet2!$B:$B,COUNTA(Sheet2!$B:$B),1)

That will expand itself automatically as you add new names into Sheet2.

Then I applied Data Validation to column B using Allow: List, Source: =Names


Now, make a choice in B2 and the rest of the info appears. Attached.

[bartow]

Ok, I almost have it now, I have substituted the spots i think i need to, only i am getting an n/a response, here is what i have....

Sheet1 name is actually Trip Ticket
Sheet2 name is actually 3rd PLT Trip Roster

everything else as far as columns remains the same.
For Trip Ticket, the table that inputs the soldiers data starts on A16
For 3rd PLT Trip Roster the data actually begins on A3

Here is what I have modified it too, where is my mistake?

=IF($B16="","",INDEX('3rd PLT Trip Roster'!A:A,MATCH($B3,'3rd PLT Trip Roster'!$B:$B,0)))

[bartow]

I must apologize I did not see the attachment you left... I was able to figure this out thank you so much.