Can Exel cross reference

[kevs1]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC

The greatest feature that I have never been able to confirm Excel can do (but I keep asking) is can you have two documents and tell Excel to cross reference one with the other and tell you which entries are in one but not the other. thanks.

 

[CyberTaz]

It sounds like you're referring to the Merge Workbooks feature (Tools menu)
BUT the workbooks involved must start as copies of the same main file - IOW,
you can't just take *any* two workbooks at random & merge them. There's
plenty of information available in Excel Help on the feature, which also
involves the matter of Shared Workbooks & Track Changes.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Mike Middleton]

kevs1 -

You use the term "cross reference," but for what you describe I would say
"compare."

A Google search for "Excel compare worksheets" yields many results.

One method (1999, Chris Nelson & Jim Rech) is to use a third worksheet to
mark differences by entering
=IF([Book1]Sheet1!A1=[Book2]Sheet1!A1,"","X")

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[kevs1]

Sorry MIke, I can't make head or tails of what that means.

 

[Mike Middleton]

kevs1 -

The formula would be useful to check to see which cells are same on the two
worksheets. So, it would be used to do a cell-by-cell comparison.

On the third worksheet, you would enter the formula in cell A1, using
references to cells A1 on the other two worksheets. Then, on the third
worksheet, you would copy the formula from A1 to all other cells that you
wanted to check.

So, you would be comparing A1 with A1, D5 with D5, C17 with C17, etc. The
formula would not check to see if the contents of A1 on one worksheet
existed at some other location on the other worksheet.

Of course, you could change the formula to be
=IF([Book1]Sheet1!A1=[Book2]Sheet1!A1,"Heads","Tails")

If you construct the formula, you could "point" to the A1 cells on the other
two worksheets. In the example, one A1 cell is on worksheet Sheet1 of
workbook Book1, and the other A1 cell is on Sheet1 of Book2.

- Mike

Sorry MIke, I can't make head or tails of what that means.

kevs1 -

You use the term "cross reference," but for what you describe I would say
"compare."

A Google search for "Excel compare worksheets" yields many results.

One method (1999, Chris Nelson & Jim Rech) is to use a third worksheet to
mark differences by entering
=IF([Book1]Sheet1!A1=[Book2]Sheet1!A1,"","X")

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[kevs1]

MIke its too convoluted. I would pay $10 for a software that just takes two excel sheets and tells you what is on one and not on the other

 

[Mike Middleton]

kevs1 -

The "too convoluted" method (using a single IF function that is copied to
other cells) is described on numerous web pages, including
http://exceltips.vitalnews.com/Pages/T002006_Comparing_Workbooks.html

And if you prefer "software" instead of an Excel function, you'll find lots
of Excel add-ins if you search Google for "excel compare worksheets." Some
are free, some may let you pay $10, and some may be compatible with your Mac
Excel 2004.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[kevs1]

thanks Mike, those all appear to the Windows plug ins, I can't find anything on mac update or version tracker