Tom,

If you have

$A$4 Value

in cell A2, then the formula

=INDEX('[Filename1.xls]Sheet1'!$1:$1,1,COLUMN(INDIRECT(LEFT(A2,FIND(" ",A2)-1))))

will return the value from A1 of Sheet1 of the file Filename1.xls.

Modify as needed to fit your file name and sheet name.

HTH,
Bernie
Excel MVP


"Tom" wrote in message ...
I utilize an installed feature in Excel that allows to
compare worksheets and - when executed - produces another
XLS file that lists the differences between Sheet1 and
Sheet2.

Data Sample of Sheet1:
A1 STATE
A2 CA
A3 IL
A4 VA

Data Sample of Sheet2:
A1 STATE
A2 CA
A3 IL
A4 TX


After executing the worksheet comparison, the VBA code
creates the additional worksheet which lists the following:

Address Difference [Book1]Sheet1 [Book1]Sheet2
$A$4 Value VA TX


In the actual data set, I have hundreds of records and
almost 50 columns.


Here's my problem/question now?
I need to translate the column letter e.g. "A" into the
actual value "State" which is stored in the 1 row.

So, rather than displaying "Value" under the "Difference"
column, I need to display "State".

I am sure if I should use a wildcard for this... Using a
wildcard (e.g. "*A*") would pick up both column A and all
2-digit columns IDs such as "AA" or "AB".

Moreover, if there's a way that I can differentiate
between e.g. $A$1 and $AA$1, how do I need to work around
the limit of 7 subnesting functions in Excel?

Any suggestions are greatly appreciated!

Thanks,
Tom