Awards
- Community Pick
- Experts Exchange Approved
Introduction
Most financial modellers will regularly need to build worksheets to either:
1) produce a "differential" sheet to show differences between two worksheets with an identical layout
2) create a "link" sheet that takes summary financial data from one WorkBook to another with live links
This is a simple process when dealing with a single contiguous data block.
For the first requirement a simple formula can be entered into the differential summary set and copied down and right to the same extent as the source data. The "link" sheet can be dealt with by copying the underlying data from Workbook 1, then using Edit - PasteSpecial and clicking on "PasteLinks".
The complication is that a typical model will have a significant amount of blank rows and/or columns, which means that a copy down and right approach is likely to produce redundant calculations that will compare blank cells in (1), or link to empty cells in (2), leaving users with the choice of a manual tidy-up or leaving the redundant calcs in place.
This Article outlines a simple non VBA approach utilising SpecialCells to quickly build these summaries ignoring any blank cells.
Producing a Differential sheet
*Note, this approach does presume that the layout of the two worksheets to be compared is identical*
- Insert a new worksheet
- Copy and paste the used portion of one of the sheets being compared to the newly inserted differential sheet. This acts as a marker for the next step, plus it provides the formatting for the differential sheet
- Goto the inserted differential sheet
- Copy and paste the used portion of the first sheet back over itself as 'Values'.
Option A) indentifying text and cells that are different
- Select all the Cells, Press F5 ….. Special …. Constants (which selects all non-blank cells)
- In the Formula Bar type a formula that refers to the top left cell in the two sheets to be compared (in the sample file, cell A4)
='2010'!A4<>'2009'!A4
And use "Ctrl & Enter" together to force this relative calculation into all the non-blank cells. Any cells that are different will evaluate to FALSE
Option B) comparing numerical cells only
- Select all the Cells, Press F5 ….. Special …. Constants ..... tick 'Numbers' only (which selects all numerical cells)
- In the Formula Bar type a formula that refers to the top left cell in the two sheets to be compared
='2010'!F4-'2009'!F4
And again use "Ctrl & Enter" together to force this relative differential calculation into all the non-blank numerical cells
Note that any numerical cells used as titles - F4 is a date year in this example - will need to be manually changed to have more appropriate cell entries (for example F4 ins the screenshot below may be more suited as 'comparison of 2009 against 2010)
Producing a Linked sheet
- Insert a new sheet in the Target Workbook
- Copy and paste the used portion of the relevant sheet from the Source Workbook to the new sheet in the Target Workbook
- Copy and paste the used portion of the new sheet in the Target Workbook back over itself as 'Values'.
- Select all the Cells, Press F5 ….. Special …. Constants (which selects all non-blank cells)
- In the Formula Bar type a formula that refers to the same cell in the Source Workbook
*Note that Excel will make this an absolute reference if you click in the Target Workbook (see screenshot) - remove the $ signs before the next step to make the reference relative. Pressing F4 twice will accomplish this*
='[EE Article.xls]2010'!A4
- Use "Ctrl & Enter" together to finalise the links back to the Source Workbook calculation into all the non-blank cells.
- In this example an additional step is required to copy the formatting from the Source Workbook back over the Target Workbook to ensure the number formats are as intended. As the date format from A4 will be inserted into the numbers in column F during the Ctrl & Enter step
Sample files
=-=-=-=-=-=-=-=-=-=-=-=-=-
If you found the article helpful please click on the "YES" button after the question below.
This utility has been helpful to me and I hope you find an opportunity to use it as well.
If you didn't or otherwise want to vote No, please leave a comment and give me an
opportunity to respond. It will give me a chance to improve this article and others I
would like to write.
If you liked this article and want to see more from this author, please click here.
=-=-=-=-=-=-=-=-=-=-=-=-=-
by: younghv on 2010-11-02 at 04:36:12ID: 21052
"Yes" vote above.