Comparing 2 Worksheets and Returning Differences

Posted by Royden Proefrock on February 24, 2000 3:31 PM

I retrieve reports from our tape cartridge silos, edit
the data in dos edit then convert the listed data in
Excel 97. What I need is to compare 2 or 3 worksheets
and return the differences into the working worksteet.
I know that INDEX, MATCH and Vlookup will come into
play but I cannot figure out how to access the
individual worksheets. All sheets are contained in
the same workspace and directory. Thanks in advance.

Posted by Celia on February 24, 2000 5:41 PM

Royden

Do you mean that within a formula you want to know how to refer to other worksheets ?
If so, the following is an example :-

=SUM(Sheet1!A1:A10)

When entering formulas, if you create them by actually selecting the other sheets and cells, Excel automatically enters the correct references

Celia

Posted by Royden Proefrock on March 07, 2000 3:11 PM

Thanks Celia!
However, I had to find a formula/function that would do the comparisons that I wanted.
I finally came up with the following:

The following function works, with the exception of returning #NA when
number does not exist. Index the entire Database, then Match Cell A1 to
corresponding List and return Value in column.

=INDEX(DATABASE,MATCH(A1103,Volume_History,0),8)

The same function as above, however testing for error code #NA is now
replaced with blank character, " ", in the TRUE test and the above function
is carried out a second time to return the number value originally sought.

=IF(ISNA(INDEX(DATABASE,MATCH(A9,Rail_Database,0),6))," ",INDEX(DATABASE,MATCH(A9,Rail_Database,0),6))

I hope this can help someone. Again thanks.