Change it to:

=IF(Sheet1!$A$1="First", Sheet1!$A1, IF(Sheet1!$B$1="First", Sheet1!
$B1,IF(Sheet1!$C$1="First", Sheet1!$C1)))

then drag fill down
--
Regards,
Tom Ogilvy


" wrote:

I have the following info on one worksheet:

Worksheet 1
A B C
Last First Middle
Smith John C
Jones Mark B

On the second worksheet, I want to be able to search the header (in
this case row 1) and reorder automatically and then autofill the data
below.

Worksheet 2
A B C
First Middle Last
John C Smith
Mark B Jones

The reason I need to do this is because I am importing the info into
worksheet one and they often times rearrange the data. I would like
to be able to not reprogram my cells every time they switch columns.

I have tried:
=IF(Sheet1!A1="First", Sheet1!A1, IF(Sheet1!B1="First", Sheet1!
B1,IF(Sheet1!C1="First", Sheet1!C1)))

This will give me the header in the right column, but then how do I
autofill the info from that specific column automatically if the
columns in worksheet 1 are rearranged?

Thanks in advance for any help you can offer.