TECHNIQUES

1-A Dilemma with Relative References

Figure 1 and Figure 2 show a simple worksheet normally and with formulas showing:

[ILLUSTRATION OMITTED]

Assume this goes on for hundreds of rows.

Also assume you discovered that most of the formulas are wrong – you really wanted those cells which refer to Data to be 3 rows down. That is, in cell D1 you wanted the reference to be to cell M20, not M17. How can you fix it? There's nothing to replace – replacing 17 with 20 certainly won't help. You can't fix the first one and fill down because of the cells which are not of a like formula – for example, filling down would destroy the formula in cell D4. Figure 3 is what you want, how can you get there?

The answer (well, one answer, anyway) is to switch to R1C1 format. Yes, it actually does have a really good use! It's done here in the File menu, Options.

How does that help? Look at the underlying formulas here:

[ILLUSTRATION OMITTED]

See those [16]'s? All you need to do is change them to [19]'s, with the result shown below:

[ILLUSTRATION OMITTED]

When you switch back to A1 notation (uncheck R1C1) you have this:

[ILLUSTRATION OMITTED]

And you're done! But wait – here's an entirely different approach which also works well. First, create a new sheet, say that's Sheet4. It will remain empty for this process, but will serve an important function as you will soon see. Change the above formulas by changing the reference from Data to Sheet4, as seen here:

[ILLUSTRATION OMITTED]

And afterwards as you see here:

[ILLUSTRATION OMITTED]

Now go to Sheet4, select cells M1:M3, Insert cells (Home tab), shifting down:

[ILLUSTRATION OMITTED]

Return to the main sheet and look at the formulas:

[ILLUSTRATION OMITTED]

Now change Sheet4 back to Data and you're done:

[ILLUSTRATION OMITTED]

2-Build a Formula with a Formula

Look at the worksheet in Figure 13 on the next page.

You can see that some information from Sheet2 is being picked up on Sheet1 via an INDIRECT formula. A quick look at Sheet2 (nothing special) in Figure 14.

Why use INDIRECT? Perhaps the information on Sheet2 may be cut/pasted elsewhere, but you are interested in the data in A1:A22, regardless of how information may be moved around.

What's the issue? Look at the formulas in Sheet1 in Figure 15.

The problem is, how can you create these formulas without typing each one (or without reverting to creating a VBA procedure!)? You can't fill the formula down from A1, because it's all text, and the formula will stay the same. The answer lies in building the formula with a formula. Let's see what this means (Figure 16).

Notice that the formulas in column A seem to be the same as the previous screenshot, but look at what's in the formula bar! You are looking at ="=INDIRECT(""Sheet2!A"&ROW(A1)&&""")" which is the formula you want to be in cell A1, but entirely built as a string except the reference to ROW(A1) to give us the one you want.

This becomes what you see in cell A1. Now when this formula is filled down, the ROW(A1) becomes ROW(A2), etc. and you have what you need. However, this is not ready for use – it's not an INDIRECT formula, it's a text string containing the word INDIRECT, etc. What's left to do is copy/paste special values. I like doing it this way especially since after the fill down, my hand is on the mouse.

Right-click the border of the selection and drag away (anywhere), and then drag the selection right back to where it started. This is what you see when you right-click drag the border away without letting go of the mouse:

[ILLUSTRATION OMITTED]

When you drag back, before letting go you see Figure 22:

[ILLUSTRATION OMITTED]

When you let go you'll see this:

[ILLUSTRATION OMITTED]

And the highlighted option is the feature you use to get this:

[ILLUSTRATION OMITTED]

It doesn't look like anything changed, but look at the formula bar – it's no longer a text string. However, the formulas in column A need to be coerced into formulas. This can be easily done by replacing "=" with "=". Replacing "=" with "=" doesn't seem like it would accomplish much, but in fact, it will force Excel to reevaluate each cell and realize that it's a formula.

You will get this:

[ILLUSTRATION OMITTED]

3-Combining a List of Values into One

A reader recently posted a question about the following. He wanted to take the numbers in column A, below, and put them in one cell, separated by commas:

[ILLUSTRATION OMITTED]

Notice cell C1 is the values in A1:A7. There are a few ways to do this (aside from re-typing the values!!) Examine them here.

The first approach might be to use the justify command, but there's a gotcha. If you try it, this happens:

[ILLUSTRATION OMITTED]

One way to solve it is this:

[ILLUSTRATION OMITTED]

Notice that the formulas in column B simply append a comma to column A. You don't want the last one (cell B7) to contain a comma, so you change that one's formula:

[ILLUSTRATION OMITTED]

Instead of using just =A7, you still append the "" which is a null string but also is text, not a number. If you read the previous alert message, you see that you can't use Justify on numbers. OK, now copy/paste special values in column B:

Make this selection (arbitrarily wide to accommodate all the values):

[ILLUSTRATION OMITTED]

and the Justify command does the job, seen in Figure 32:

[ILLUSTRATION OMITTED]

OK, I mentioned other ways. Here's one. Look at the formula in B2:

[ILLUSTRATION OMITTED]

Start in B2 because the cell above needs to be referenced to see if it is empty. If the cell above is empty, use A1 and append a comma, otherwise take the cell above and append it to the cell to the left. As you can see, it grows as you fill down. The formula in B3 is =IF(B2="",A2&", ",B2&A2&&","). So the answer is in cell B8 — all you need do is copy/paste values for B8 and chop off the trailing comma.

4-Comparing Worksheets

Suppose you need to see if 2 worksheets are identical. You may try to do this visually or maybe even use VBA to find the differences, but let's see if there's an easy way. Look at these 2 sheets from the same workbook:

[ILLUSTRATION OMITTED]

Notice that when looking at Sheet1 and Sheet2, they certainly look the same, but there are differences. A pretty simple way to see the differences is by using a third worksheet (or even another workbook), and entering this simple formula:

[ILLUSTRATION OMITTED]

It's comparing cell A1 (as a relative reference) in Sheet1 to Sheet2, and when there's a difference, it puts in a bullet (Alt+7 (7 from the numeric keypad)). So, looking at this, you can see where there are differences:

[ILLUSTRATION OMITTED]

Cell C3 looks the same in both sheets, but on further examination you could determine that there's a trailing space in one and not in the other. Cell A5 ends in 751 on Sheet1 and 753 on Sheet2.

If you wanted to compare not just values but differences in formulas, you would have to make a change first. A formula such as =1+3 would be treated the same as =2+2 since Excel is comparing the results. You would...