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

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

Bernie:

Thanks for your prompt reply... I am not sure though if I
understand this correctly or if there's a disconnect.

Based on the error value "$A$5" I will need to pick
column A, row 1. If the error would have read "$Z$15" I
would have to point to column Z; row 1. Row 1 needs to
be selected for all errors since it contains the column
name.

Additionally, I need for another value translation, I now
need to point to any column (let's say column B), but
here the row number of the error is the driver.

This is because I contain record IDs in column B... but
again, depending on the row value of the error. So, for
an error value of "$Z$66", I need to point to B66 on
Sheet1. Makes sense?

I appreciate if you have additional input for me!!!

Thanks,
Tom


-----Original Message-----
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


.

Tom,

I'm not sure what is in your column A: is it just the Address String
$A$4
or is it Address String and Value
$A$4 Value

If it is the first, my formula won't work, and should be changed to
=INDEX('[Filename1.xls]Sheet1'!$1:$1,1,COLUMN(INDIRECT(A2)))

If the Value is $Z$4 instead of $A$4, the formula will return the contents of Z1 of Sheet1.

As to the second part:

=INDEX(Sheet1!$B:$B,ROW(INDIRECT(A2)),1)

IF - A2 only holds a cell address string.

HTH,
Bernie
Excel MVP


"Tom" wrote in message ...
Bernie:

Thanks for your prompt reply... I am not sure though if I
understand this correctly or if there's a disconnect.

Based on the error value "$A$5" I will need to pick
column A, row 1. If the error would have read "$Z$15" I
would have to point to column Z; row 1. Row 1 needs to
be selected for all errors since it contains the column
name.

Additionally, I need for another value translation, I now
need to point to any column (let's say column B), but
here the row number of the error is the driver.

This is because I contain record IDs in column B... but
again, depending on the row value of the error. So, for
an error value of "$Z$66", I need to point to B66 on
Sheet1. Makes sense?

I appreciate if you have additional input for me!!!

Thanks,
Tom


-----Original Message-----
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


.