"Paste Link" based on specific column headers

[SalesGuy]

I have 80 columns of raw data with static headers that are produced out of an ERP system, however the report run is not consistent in placing the data in consistent column letters. I am looking for a solution to auto-copy the raw data into a formatted worksheet that can identify the data based on the sheet name and column header, similar to Paste Link where a column would populate, but only based on the Column Header name alone.

In a beautiful world it would look something like "='Raw Data'!"Data Column Header".

I'm a total Excel idiot so if you can recommend a macro, please be gentle.

Thanks so much in advance!

Todd

 

[Stephen_Tsui]

SalesGuy:

Is below Result what u require?
If yes just use the Hlookup Formula as below, and Copy the A2 Formula to cells under column header

Excel Workbook
	A	B	C	D	E	F	G
1	Name	Date	Hello	Bye	Amount	Testing	Go Ahead
2	Sally	12/11/2010	A	Bye	1256	C	D
3	David	22/12/2010	A	Bye	1437	C	D
4	Tom	15/11/2010	A	Bye	1290	C	D
5	Mandy	14/9/2010	A	Bye	1786	C	D
Raw Data

Excel Workbook
	A	B	C	D	E	F	G
1	Date	Name	Bye	Hello	Testing	Go Ahead	Amount
2	12/11/2010	Sally	Bye	A	C	D	1256
3	22/12/2010	David	Bye	A	C	D	1437
4	15/11/2010	Tom	Bye	A	C	D	1290
5	14/9/2010	Mandy	Bye	A	C	D	1786
Result