Xcel 2007Fill details from sheet 1 to sheet 2

[dsmith910]

Help! I have a list of members (complete with details such as address, tel number etc) on sheet 1. Sheet 2 is a membership form. each member has a unique membership number.

I want to physically type the membership number at the top of Sheet 2 and all the details are automatically transferred to sheet 2.

I know I could do this easily by putting the application form in word but would prefer if possible to keep everything together in Excel.

Naturally the Membership Form has the member's details spread across different columns/rows in a 'pretty' format.

 

[dsmith910]

sorry! put this thread on in a panic as I need to get this done fast.

Should have mentioned that I have already put part of the 'calc' in the fields i.e. =Members!E3 automatically puts the members name in. Then I realised that I will have to change the 'E3' to 'E4' or whatever for each sheet I fill in - there are currently 14 fields to complete and about 30 members - so quite a task.

If I put sheet 1 into member number order can I then put in the members name field on sheet 2 something which links the member number I physically type in A2 of sheet 2 to the same member detail line on sheet 1.

Hope this is a little clearer
Thanks

 

[briand2]

If the Sheet 1 data is in tabular format, with one row per member and unique membership number on the left-most column, then you could use VLOOKUP:

Say, for example, all Sheet 1 data is in range A1:F10, with column A holding unique membership number and column C holding house number and column F holding age:

In Sheet 2, say cell A1 holds the unique membership number.

In Sheet 2, say cell A3 is to repeat house number, then put in cell A3 this formula:

=VLOOKUP($A$1, Sheet1!$A$1:$F$10,3, FALSE)

In Sheet 2, say cell A5 is to repeat member's age, then put in cell A3 this formula:

=VLOOKUP($A$1, Sheet1!$A$1:$F$10,6, FALSE)

Hope this helps.

Brian