Help w formula

  • Thread starter Thread starter alex
  • Start date Start date
A

alex

Hi, I created an excel file that receives exported data
from manarch pro. I use a second excel sheet in the
workbook to make the data easily readible. Currently I
just use = in a cell on sheet 2 to reference the data on
sheet1. The problem is that that formula only references
the row and column number. I need a formula that looks
for a name in sheet one and transposes the row of data to
sheet 2 w the name. In other words I would like row 2in
sheet 2 to reference say the "kevin: in sheet one and if
found pull the data for the row in sheet 1 were the name
kevin is present. The location of the name Kevin in sheet
one may vary from mnth to mnth. Thanks for the help Alex
 
Alex

Try a VLOOKUP formula on sheet2 to find Kevin on sheet1 and move his data to
sheet2.

Sheet1...........

Assuming a list of names Pete, Alex, Gord, Kevin in column A.

Assuming each name has a row of data that extends over 6 columns B to G.

Select your range A1:G4 Insert>Name>Define. Enter a name for this range(e.g.
TheTable)

Sheet2..........

In B1 enter this formula =VLOOKUP($A$1,TheTable, COLUMN(),FALSE)

Drag/copy it across to column G

Enter the name Kevin in A1.

No matter where Kevin is located in Sheet1 Column A, his row of data will be
returned across Sheet2 Row 1.

Enter Alex in A1 and get Alex's data. Add a Data Validation list in A1 and
you don't even have to type in a name. Just point and click.

Post back if this is not what you need. We can always take another crack at
it.

Gord Dibben Excel MVP - XL97 SR2 & XL2002
 
Back
Top