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 need to do a global replace of "=" with "x=", changing all formulas to text, then the comparison sheet would show any differences. Once found and corrected, you could then change "x=" to "=".

5-Creating a Series of the 15th of the Month and the Last Day of the Month

It's not too difficult to create Figure 37 without using formulas.

How would you go about doing it? You can't use any series, because if you tried by starting to use Figure 38, and drag the Fill Handle, you'd see this mess in Figure 39 and so on:

[ILLUSTRATIONS OMITTED]

Here are two approaches: First, enter 1/15/11. Select the date and the following blank cell as shown in Figure 40. Right-mouse drag the Fill Handle. When you let go of the right mouse button, you'll see Figure 41, and when you select Fill Months, you'll get Figure 42:

[ILLUSTRATIONS OMITTED]

Now, you can Go To Special (on the home tab, or press F5, click Special) – see Figure 43. Select blanks (Figure 44). And you will have Figure 45. Now, with cell A2 active, enter =A3-15 and instead of pressing Enter, press Ctrl+Enter to see Figure 46. You can manually enter 12/31/2011 in cell A24.

Here's a second approach: After having the 1/15/2011 in A1, and blank in A2, then 2/15/2011 in A3, etc., enter this in C1:

[ILLUSTRATION OMITTED]

Again use the right-click fill handle down to C24 and select months to generate the end of each month (Figure 48). Next, copy this range (C1:C24), click in cell A2 (not A1), and Paste Special. Be sure to include Skip Blanks (Figure 49):

[ILLUSTRATION OMITTED]

You will wind up with this:

[ILLUSTRATION OMITTED]

Now clear column C.

A third approach: Enter 1/15/2011 and 1/31/2011 in A1:A2. Select both cells. Right-drag the fill handle down to A24. When you release the fill handle, choose Fill Months from flyout menu that appears.

6-Match Colors

Suppose you inherit the worksheet shown here:

[ILLUSTRATION OMITTED]

You want to make the FONT of column E equal to the color in A1:C7. How do you find the correct color? If you click on the arrow next to the Fill Color icon on the Home tab in the Font group, then click More Colors:

[ILLUSTRATION OMITTED]

When you click on the "Custom" tab, you'll see Figure 53:

[ILLUSTRATION OMITTED]

The RGB numbers (Red, Green, Blue) are the numbers which make up the color. You can now use these in the Font Color to make the font the same. Select column E, use the drop down arrow next to the Font Color icon, select the Custom tab and enter the same values Then once you type something into column E it will be the same color.

There is another way to do this using the VBE (Visual Basic Environment). Press Alt+F11, then Ctrl+G (to get the immediate window), and enter this code:

[ILLUSTRATION OMITTED]

7-Reorganization of Data

Look at the spreadsheet:

[ILLUSTRATION OMITTED]

Suppose this continues down hundreds of rows. Suppose you wanted the data to be rearranged so that it looks like this:

[ILLUSTRATION OMITTED]

As well as filled down.

The setup is not intuitive. You would like the formulas in C1:K2, above, to be:

[ILLUSTRATION OMITTED]

If you set up the initial two rows like Figure 58 above and fill down, the result is what you see in Figure 59:

[ILLUSTRATION OMITTED]

Which is definitely not going to work.

But if you set it up manually as shown here:

[ILLUSTRATION OMITTED]

where you reference column A in column C (using x instead of "=", so this is all text), then select D1 and use the fill handle to drag to the right, you see this:

[ILLUSTRATION OMITTED]

Which becomes this when you let go of the mouse:

[ILLUSTRATION OMITTED]

Repeat with D2:

[ILLUSTRATION OMITTED]

Now select C1:K2 and use the fill handle to drag down and you will get this:

[ILLUSTRATION OMITTED]

Lastly, replace "x" with "=":

[ILLUSTRATION OMITTED]

Resulting in this:

[ILLUSTRATION OMITTED]

You now have the formula you want. You are seeing the formula because the worksheet is set to Show Formulas which is easily toggled by Ctrl+`. When you use the toggle again, and adjust the column widths, you see this:

[ILLUSTRATION OMITTED]

8-Scrolling Text

Since this is a book, I can't really show you what scrolling text looks like. Here are a few screenshots from pressing the button once. Look at Figure 68 through Figure 73:

[ILLUSTRATIONS OMITTED]

and after a while,

The meat of the text is kept outside anyone's view, as seen in Figure 74, and it continues to Figure 75:

[ILLUSTRATION OMITTED]

The value in X1 is key to the scrolling process. The first thing you need is to take a picture of some cells, using the camera tool. In order to make this work properly, it has to be put into the QAT. You'll need to do that first by following these steps: Right-click anywhere on the ribbon, and select "Customize Quick Access Toolbar ..." and you'll see Figure 76 (showing the top left corner only):

[ILLUSTRATION OMITTED]

Change Popular Commands to "All Commands":

[ILLUSTRATION OMITTED]

Scroll to Camera, and click Add:

[ILLUSTRATION OMITTED]

Select any cell, click the camera tool, then click where you want the scrolling to show, like near cell D1:

[ILLUSTRATION OMITTED]

The formula bar shows H8 because that was the arbitrary cell you had selected when you clicked the tool. Next, you need to decide how many rows of data you want to show in the scrolling. Perhaps five, which you would set up in a defined name which you might call "Piece":

[ILLUSTRATION OMITTED]

Notice that it's an offset of cell W1, where all the "real" text to scroll exists, and it's offset by whatever number is in cell X1. Then the shape is determined to be five rows by one column. When you next change the $H$8 in the formula bar to Piece, you see the new shape:

[ILLUSTRATION OMITTED]

While its selected, press Ctrl+1 to bring up the Format dialog, and it will show no lines:

[ILLUSTRATION OMITTED]

Now, when you click off of the picture, you will see nothing:

[ILLUSTRATION OMITTED]

Next, you need to look at the macro. Press Alt+F11, use Insert/Module, and enter this simple code:

[ILLUSTRATION OMITTED]

There's a loop in which the variable i goes from 0 to 43, because there are 39 rows of information in the text to scroll, and you want it to scroll beyond it, out of view. Computers are fast, so each time through the "i" loop you calculate (so the Offset updates and the new text shows up in the picture), and then a very tight do-nothing loop where j goes from 1 to 20,000,000! You can adjust this value for speed. The larger the number, the slower the scroll.

The colon separates the statement and acts like a new line. Figure 85 and Figure 86 are equivalent:

[ILLUSTRATIONS OMITTED]

Lastly, you need to link the button to the code (see Figure 88 and Figure 89 to see how to put a Forms button on the sheet), which is done by right-clicking the button, selecting Assign Macro:

[ILLUSTRATION OMITTED]

If the macro was written before creating the button, then as soon as the button is created you'd be presented with the form to link it to the macro as seen in Figure 88 and Figure 89:

[ILLUSTRATIONS OMITTED]

In case you don't see the Developer tab, you can have it show by right-clicking any part of the ribbon, select Customize the Ribbon:

[ILLUSTRATION OMITTED]

Check the Developer checkbox:

[ILLUSTRATION OMITTED]

If you are using Excel 2007, you will see the access to the Developer tab in the Office button, Excel Options, Popular category, Show Developer Tab In The Ribbon.

9-Sequences

Suppose you're keeping track of coin flipping and you want to gather some information on the longest sequence of heads; or how many times a sequence of 4 heads came up, etc. Figure 92 shows a sample worksheet:

[ILLUSTRATION OMITTED]

One approach to start is to match "T" as an offset:

The formula

in B1, filled down yields these numbers:

[ILLUSTRATION OMITTED]

It's an interesting start – the 6 in B2 shows a string of six heads, and the 2 in B9 shows a string of two heads, etc., but you need to distinguish the 2 in B6, B14, and B18 as not being significant or useful, since these are not the beginning values of the counts. The -1 at the end of the MATCH formula is needed to get a true count. Matching a "T" from cell B2 would find it in the seventh cell down, indicating 6 heads, hence you need to subtract 1. The 100 in the OFFSET function is an arbitrarily large enough number to ensure finding a "T" (there likely won't be 100 heads in a row!) – and you can change this to 1000.

So how can you get rid of the extraneous values? Look at this:

[ILLUSTRATION OMITTED]

Now you are using the formula only if the cell above is a "T". That is, once you have encountered a "T", you can start over, and eliminate the not useful values by putting in a -1. Basically, the formula is saying that if the cell above and to the left is a head, put in -1 because you don't want it (a zero would work just as well). You are only interested in the first head. You can see from the above figure that the interesting values are in B2, B9, and B12 – there are strings of six heads, another of two heads, one of four heads, etc.

Now you can gather some statistics on these values, as shown below (the formula could just as well have been =COUNTIF($B:$B,F3) instead of B1:B28:

[ILLUSTRATION OMITTED]

10-Variable Linked Cell

This figure contains a simple Forms combobox (not ActiveX):

[ILLUSTRATION OMITTED]

Choose Item5:

[ILLUSTRATION OMITTED]

So far, so good. But look what happens when you choose item10:

[ILLUSTRATION OMITTED]

Notice item 10 is placed in F2, where item5 was placed in F1.

Now choose item 1 ...

As you can see, the choices are moving down column F. How is this happening? Well, first look at the formulas in column F:

[ILLUSTRATION OMITTED]

Something's happening out in columns M and N. Column M simply contains the list for the Combobox control, and column N looks like this:

[ILLUSTRATION OMITTED]

Nothing at all fancy in N2, is there? But what's that 3 in L1 as seen above?

Ah – the plot thickens! L1 is counting how many numbers are in column N, and at this point there are 3, as you can see. So where's the magic? If you right-mouse click the dropdown, and select Format Control:

[ILLUSTRATION OMITTED]