Michelle asks "I think I'm on the right track with a circular reference. Here's my problem. I have two cells, A1=5 and B1=5. What I want to do is have B1 memorize 5, and then I want to add a new value to A1, say A1 now equals 10. Can I have a B1 formula that memorizes 5, but also adds 10? So now B1=15?"

Usually, circular references are a bad thing, but sometimes they can be used to our advantage. Here is the non-macro way of doing what you want to do. It will only work in certain situations.

• From the Excel menu, select Tools > Options.
  
• Go to the Calculation tab. Check the box for Iterations. Change Maximum Iterations to 1.
  
• Click OK to close the options dialog box.
  
• Enter 5 in Cell A1.
  
• Enter 0 in Cell B1
  
• Enter =A1+B1 in Cell B1
  
• Now, as you enter new values in A1, the entry in B1 will remember the old total and add the value from A1.
  

Here is the HUGE limitation. You can not enter any values elsewhere on the sheet! Any time that you enter a value or the sheet is recalculated, the value in A1 will be added to the value in B1. Thus, by hitting F9 a bunch of times, you will watch B1 increase by 5 for each F9.

The safer way to do this is with a little event handler macro. You will need to add this code to the code pane for Sheet1 (assuming that you are working on Sheet1). For an introduction to entering event handler macros, review http://www.mrexcel.com/tip055.shtml. The event handler code would be as follows:


Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Application.EnableEvents = False
        Range("B1").Value = Range("B1").Value + Target.Value
        Application.EnableEvents = True
    End If
End Sub


This bit of code is run any time that a cell is changed on the sheet. Target is a special object variable that tells which cell was changed. The event handler checks to see which cell was just changed. If the cell was A1, then it will add the value in A1 to B1. We have to turn off event handlers while changing B1 so that the event handler doesn't call itself again.

Jandy asks, "Hi, I've searched Excel Help, the net (including MS Knowledge Base & several user groups) and your wonderful site without success - is there a way to format existing numbers as text without having to tap F2-ENTER for every blessed cell? I often need to reformat hundreds of numbers but can no longer stand the boredom, not to mention the risk of repetetive stress injury."

I suspect that Jandy has a column of numbers. After she is given the spreadsheet, she formatst the column as text using Format > Cells > Number > Text.
Then, by editing each cell with F2, the cell will change from numeric to text. This can be a very mundane solution to the problem.

The workaround is a little complicated, but worth learning. For this example, let's assume that you have numbers in cells A2:A1000.

• Insert a temporary blank column B.
  
• In cell B2, enter this formula: =TEXT(A2,"0")
  
• Copy the formula in B2 down to B3:B1000
  
• We need to change these formula to values in order to have them become text. Highlight cells B2:B1000. Use Ctrl+C to Copy, then Edit > PasteSpecial > Values > OK. The entries in column B will now be text versions of the numbers in column A.
  
• Copy column B back to Column A. Delete the temporary column A
  

The key to this technique is the =TEXT() function. The 2nd parameter describes how the number should be formatted before being converted to text. You may need to adjust this based on your numbers. The result of =TEXT(123.25,"0") will be 123. The result of TEXT(123.25,"0.0") will be 123.3. The result of TEXT(123.25,"0.00") will be 123.25. To always keep only the decimals as entered, use =TEXT(A2,"General").

This function is also great for converting dates to formatted dates. If you have 5/29/2003 in a cell, then using =TEXT(A2,"d mmmm, yyyy") will give you 29 May 2003.

Another method is to highlight the column of numbers and use Data > Text to Columns. Indicate that you have formatted text. In step 2, remove any column markers that may have shown up. In step 3, indicate that this column is text.

Kevin asks, "I have a set of data that is stored in a tab delimited text file. Some of the information needs to retain the "padding" - such as Zip Codes (they need to be 5 or 9 characters in length). When the text file is opened into excel, the Text Import Wizard automatically comes up. There is an option to specify that the column containing that data is Text rather than Generic. Selecting this option works - but it requires me to do the open manually. I have not been able to locate a way in VBA to have the information be Text rather than Generic - and saving my 'padding'."

Turn on the macro recorder and record the process of importing your text file. Here is the resulting code:

Workbooks.OpenText Filename:= _
"C:\TestData.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 2), Array(4, 1)), TrailingMinusNumbers:=True

The FieldInfo parameter controls how each field is imported. In this case, the macro recorder noted that you have 4 fields. The Array(2, 1) entry says that for the 2nd field, use a "General" field type. Note that for field 3, the entry is Array(3, 2). The "2" is the code for Excel to import this field as Text.

Roberto asks again, "When I scroll down through the hundreds of rows on my spreadsheet, I always want to see the headings in Row 1. How can I do this?"

Scroll to the top of the spreadsheet so that you can see rows 1 and 2. Click in cell A2 so that this is the activecell.

From the menu, select Window > Freeze Panes. Anything visible on the screen above the activecell will be visible as you scroll through the worksheet. Also note that anything visible on the screen to the left of the activecell would remain locked as you scrolled across the columns. Since you only wanted to freeze row 1, it was important to have the cell pointer in column A, so that no columns were included in the Frozen section of the worksheet.

Roberto asks, "I have a row of headings at the top of my spreadsheet. How can I make this row appear at the top of each page when I print out the spreadsheet?"

From the menu, select File then Page Setup.

In the Page Setup dialog, there are four tabs across the top. Select the Sheet tab. In the field for Rows to repeat at top:, enter 1:1
This will cause row 1 to appear at the top of each printed page.

Bob asks, "I've seen a spreadsheet where for each cell in column A, they managed to have 2 or 3 rows in column B. How do I have cell A1 correspond with 3 rows in column B?"

This effect is achieved by using the merge cells feature to merge cells A1, A2, and A3.

Here is how to to this:
Select cells A1 through A3.
From the menu, select Format, then Cells
On the Format Cells tab, go to the Alignment tab. Near the bottom left, check the box for Merge Cells. This will ensure that cell A1 is 3 rows high and will correspond to cells B1, B2, and B3.

The Excel default is that all text is aligned with the bottom of the cell. This would make your heading in cell A1 line up roughly with cell B3 instead of B1. I prefer to have the text in A1 appear at the top of the cell, so while you are in the Format Cells dialog, change the Vertical: dropdown to "Top".

Keep in mind that there are limitations when using merged cells. You may not be able to sort this range, nor can you paste another range here that does not have identically merged cells.

Wow! I just walked into my local Barnes & Noble bookstore and found a copy of Mr Excel ON EXCEL on the shelf. That was a real thrill. In fact, Barnes & Noble and several fine independent bookstores have stocked the book on their shelves.

I want to offer my thanks to these bookstores for stocking the book. I also want to make sure that there is plenty of demand for the books. I want you to get away from the computer, drive over to Barnes & Noble at lunch and buy the book. Yes, I know you can buy the book online. Yes, I know you can probably save a few bucks and avoid paying sales tax by buying online. However, look outside... it is a nice day. Take a drive. Buy the book.

I have a special offer for all MrExcel readers from the U.S. and Canada. If you walk into your local bookstore between now and June 15th and buy any of my books or CDs, I will give you a FREE copy of the MrExcel Knowledge Base CD (a $49 value) for just $1 shipping. See complete details at http://www.mrexcel.com/rebate.html

Congratulations to Colo and Gareth on the birth of their new site. Visit the Excel Maniacs at http://www.geocities.jp/xlmaniacs/