Frank from Holland posed today's question: Hello, I want to make a sort of database in Excel. Therefore, I need to find a Macro or something like that, with which I can create a command that makes the cursor jump from (for example) A16 to B2 after using the enter-button on A16. Would you know the solution for this problem? Thanks!

One easy way to achieve this is to make A16 and B2 the only unlocked cells on the worksheet. By default, all cells are locked. You can use Format - Cells - Protection - uncheck the Locked checkbox to unlock the cells.

In Excel 2000 and earlier you can simply protect the worksheet with Tools - Protection - Protect Sheet. In Excel 2002 you will have to use Tools - Protection - Protect Sheet and then uncheck the option for "Select locked cells".

After the sheet is successfully protected, hitting enter in one unprotected cell will take you to the next unprotected cell.

If protecting the worksheet is not practical, you would have to resort to a Worksheet_Change macro that sensed which cell just changed. For information on how to enter worksheet event handler macros, see tip055

Here is a sample macro:

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case Target.Address

        Case "$B$2"

            Range("A16").Select

        Case "$A$16"

            Range("B2").Select

    End Select

End Sub

Hope to have the message board back tomorrow.

Bill sends today's question: How can I stop the confirmation box from needing to be clicked "OK" when deleting files or sheets called to be deleted within a MACRO under Excel 97.

Use the DisplayAlerts property:

Application.DisplayAlerts = False
Worksheets("MySheet").Delete
Application.DisplayAlerts = True

The message board is down. Everything looks OK to me, I am checking with the company that hosts MrExcel to see if anything has changed on their end. I apologize for the problem. I'll post status updates here.

Bill

Melinda writes: Is there any way to merge two rows together quickly? The idea is to fill in the blank fields in row A with the information contained in row B.

I will soon have about 400 spreadsheets with up to 6000 rows each in which I will have to apply the above process. And of course this has to be done in a three day turn-around period.

Can you help??????

Yes. You need to highlight all 6000 rows. Edit - GoTo. Click the Special button. Click Blanks and then OK.

You now have all of the blanks selected. You are going to enter a formula to copy the value from the cell above. Type the equals sign, hit the up arrow, then hit Ctrl+Enter to enter this formula in all of the blank cells.

Finally, reselect all 6000 rows. Edit - Copy, Edit - PasteSpecial - Values - OK.

This is documented in one of the early tips of the week - Tip004

Happy Birthday to MrExcel.com! Three years ago today, this site was launched. It took four days before the first two questions came in. I eagerly opened them both up and had no clue how to answer either one. I thought, "Oh no - maybe this was a mistake...".

Since then, traffic has picked up tremendously. I am about to hit my one-millionth page view for the year 2001. There is enough consulting work to keep myself busy, plus more projects for Juan Pablo and Mala and occassionally Access projects for Mike and data analysis for John.

People are simply amazed when they see what can be done with Excel/VBA. For all of this, I am very thankful. Tomorrow is Thanksgiving in the United States, and I would like to offer my thanks to the loyal readers & clients who have made MrExcel.com a success.

Todays question: My question sounds simple but I cannot figure this out. I work at a Call Center and I'm importing info from reports created by the Avaya System (phone system). Now, this reports have talk time, wrap up time, and other stats.
When I import the stats to Excel if the time is less than 1 min it appears as :01 sec. What I need is for Excel to change this to 0:01. The main reason I need that zero is because when I transfer this info to Access it does not recognize anything that does not appear in this format (0:00). I have played with the Formatting and have yet to figure this out. It is very time
consuming to sit there and add zeros to fix this! My knowledge of Excel is very basic. Can you build a macro? Or what about conditional formatting?

I would use this simple macro. First, highlight the selection with the times, then run the macro.

Sub TryThis()
    For each cell in Selection
        if left(cell.value, 1) = ":" then
            cell.value = "0" & cell.value
        end if
    Next cell
End Sub

For all of our U.S. readers, have a great Thanksgiving weekend!

Scott sent in this excellent problem, which he had 98% of the way solved: I have two spreadsheets. One of which, I enter data into. The other is a mirror of the first using the paste link feature. This works great. You can 'see' the first sheet by looking at the second sheet.

Under perfect circumstances, sheet 2, cell A10 shows me the data in sheet 1 cell A10. Now, let's say that there is a value of "a-ten" in cell A10 of sheet 1. If I insert a row into the sheet 1 between rows 9 and 10, this bumps A10's value of "a-ten" to A11.

Looking at my second sheet, cell A10 still shows "a-ten", but when you look at the address, the reference is now A11. Cell A9 will be referencing A9 as it should. But, it essentially created a vacuum where data from sheet 1 will fall through the cracks.

Excel is tracking the data that used to be in cell A10, not what is actually in A10.
I would like to track what is physically in A10. not where the data in A10 goes.

My idea to beat Excel at it's own game is to somehow create a reference that uses a combination of ROW and ADDRESS functions like this.

Placing this in the second sheet
=(ADDRESS(ROW(),1,2,,"='[workbook]sheet1'!"))
gives me the text of the reference I want. Is there any way to convert that into an actual reference?

The INDIRECT() function will take text that looks like a reference and will give you the actual value at that reference. So, your formula would be:
=INDIRECT(ADDRESS(ROW(),1,2,,"='[workbook]sheet1'!"))

Lee writes "How do I capitalize the values in a particular column? Do I use the function "UPPER" - if so, what is the complete formula? Thanks very much for your help."

There are two possibilities. =UPPER() will change the text to all upper case. =PROPER() will change the text to proper case.
If cell A2 has the word "john smith", use these formulas:
In B2: =UPPER(A2) to return JOHN SMITH
In C2: =PROPER(A2) to return John Smith

Copy the formula from B2 down to all of the rows where you have data in A.

You can then highlight these cells in column B, Edit - Copy, highlight A2, then Edit - PasteSpecial - Values - OK to overwrite the original values in A.

One note about =PROPER(). It works fairly well, but "mcdonald" changes to "Mcdonald" instead of the generally accepted "McDonald".

Cathy writes" I created a column chart in Excel. There are 5 data points, and the text is appearing along the x-axis for columns 1, 3 and 5, but it is missing for columns 2 and 4. I checked the worksheet, and the text is appearing in the table. Why isn't it appearing in the chart?

If you create a chart in a worksheet, you may find that Excel sets this option by default. You may find that you don't like some of the chart options set by Excel. The great news is that everything in a chart is customizable. All of those settings are just a right-click away.

In this case, go to the X axis and right click the words for the first data point. From the pop-up menu, select Format axis. There are five tabs across the Format Axis dialog. This particular setting is on the Scale tab. On the Scale tab, change the value for "Number of Categories between Tick Mark Labels from 2 to 1.

Gareth from England writes and asks about having a drop-down list on one worksheet where the list of valid cells is on another worksheet. Gareth also notes that he would prefer not to use activeX combo boxes on the sheet, as they do not move and resize with the cells.

The answer to this problem is using Data Validation. This feature, introduced in Excel 97 offers a simple way to have a drop down list right in the cell. There is no problem with moving or resizing.

This is a great feature to know. I am embarrassed that after 50+ tips of the week, I have never fully explained this feature. That will come soon. For now, you can review these two tips that touch on the issue:

http://www.mrexcel.com/tip046.shtml touches on the issue, although without any screen shots.
http://www.mrexcel.com/tip008.shtml covers a more complex example of Data Validation.

The simple case of setting up Data Validation is easy. Go to an out-of-the way place on your worksheet and enter the list of values. Go to the cell where you want the dropdown to appear. From the menu, select Data, Validation. In the allow box, choose List. In the list box, use the RefEdit (red and white icon at the right) and select your range. That is all there is to setting up a data validation.

Here is the real tip for today: Generally, when you try to set up a data validation and you enter a list that is on another sheet, for example if you enter "=Sheet2!A1:A99", you will get an error saying that "You may not use references to other worksheets for data validation criteria". There is a way around this limitation! Go to Sheet2, highlight your list range and give it a name with Insert - Names - Define. If you assign A1:A99 on Sheet2 the range name of MyList (or anything you can remember). Then go to your original sheet, and in the Data Validation dialog, for the List, enter =MyList and you will be able to have the validations on another worksheet.

Finally, I have a favor to ask: This morning, I surfed to MrExcel.com in order to find the links above, and I was greeted with an annoying popup advertising window that opened in a new browser. My agreement with Burst Media prohibits these pop-up windows. They are annoying, rude and I don't want them on the site. Please, if you are a regular reader of MrExcel and you ever notice a pop-up window coming from this site, please immediately send a message to Bill@MrExcel.com so that I can take issue with the ad company. I am off to do that right now. Thanks for your help, and sorry for the annoying pop-ups.

Salim asks: I have a number with 8 digits. How can I format it to appear as 12/3456/78?

From the menu, select Format - Cells. On the numeric tab, select Custom. In the custom box, type
99\/9999\/99

When you are typing custom number formats, you can insert any single text character by preceeding it with a backslash.

Long-time readers will recognize a new look at MrExcel.com. MrExcel sends out thanks to Karen at Matrix Business Communications. Karen does writing, editing, document design & layout for clients around the world. She does excellent work at reasonable rates. If you have a document that could use some professional editing, contact Karen and tell her MrExcel sent you.

Bill writes: I have a document that has become very unstable. Every time that my mouse gets near one of the red comment triangles, Excel will crash.

After looking at Bill's worksheet, I found a somewhat innocuous macro that was causing the problem. In an effort to clear out some pictures and command buttons on the sheet, Bill had the following code:
For each sh in ActiveSheet.Shapes
    sh.Delete
Next sh

I set up a new test workbook and was able to duplicate the problem. Apparently, the yellow box that pops up when you hover over a cell's red comment indicator is a Shape. I ran the above code, this time asking for a MsgBox sh.Name. Sure enough, included on among the pictures and buttons were one shape for each comment. Deleting this shape left the worksheet in a corrupt condition.

The workaround for this book was to remove the sheet and insert a new one without the broken comments. I also suggested changing the macro to this:
&Nbsp;&Nbsp;&Nbsp;&Nbsp;If Not Left(sh.Name, 7) = "Comment" Then sh.Delete

Someone (why the rash of people not giving their name?) wrote: I have a column of 50 numbers and want to randomly select 5 of them. Can this be done?

There are several approaches to this. Here is one way to accomplish this. Let's say that your 50 numbers are in cells A2:A51.

• Highlight cells B2:B51
  
• Enter the formula =RAND() and hit Ctrl+Enter. This will enter the formula in all 50 cells at once
  
• Copy B2:B51 with Ctrl+c
  
• PasteSpecial the formulas into values by using Edit - PasteSpecial - Values - OK
  

You have now assigned a random number to each row. You could sort by column B and take the top 5, the bottom 5, whichever you prefer. If for some reason you can not sort the data, you could use column C and enter a Rank function.

• In cell C2, enter this formula: =RANK(B2,$B$2:$B$51)<6
  
• Copy the formula from C2 to C2:C51
  

Anything with a value of TRUE is one of the 5 randomly selected numbers

Lloyd writes: I attempted to use the F2/Edit function in the record macro mode. It would not work. It appears when you are in the F2 mode, record is turned off. Is this true?

What I need to do throughout a big worksheet was to edit the cell, move the cursor to the end of the cell and then delete the last 4 characters if the characters.

This macro should do it:

Sub RemoveLast4()

    For Each cell In ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)

        cell.Value = Left(cell.Value, Len(cell.Value) - 4)

    Next cell

End Sub

"S" (why can't people sign their e-mail with at least a first name?) wrote today's question. I am trying to automatically put page breaks into a table in Excel. I tried Data - Subtotals, but that forces me to add a Sum or a Count. I don't want any subtotals, just a page break whenever the value in column A changes.

This macro will solve the problem. For some reason, macros that change page breaks or page setups take a long time to run, but this will certainly be faster that doing the process manually. The macro assumes your data is in column A and that it starts in row 2.

Sub AddBreaks()

    StartRow = 2

    FinalRow = Range("A65536").End(xlUp).Row

    LastVal = Cells(StartRow, 1).Value

    

    For i = StartRow To FinalRow

        ThisVal = Cells(i, 1).Value

        If Not ThisVal = LastVal Then

            ActiveSheet.HPageBreaks.Add _

                before:=Cells(i, 1)

        End If

        LastVal = ThisVal

    Next i

End Sub

Janet from England posed today's question. We need to know how to set up a macro which will assign a unique invoice number, how to set up a second macro which will save the completed invoice as the unique number.

Here is the hypothetical application that I am imagining. There is a workbook with an Invoice worksheet and a Menu worksheet. I propose storing the last invoice number on an out of the way cell on the Menu worksheet, lets say Z1. If the invoice number is supposed to go in cell H2 of the invoice worksheet, then this macro would do the trick:

Sub GetNextInvoiceNumber()

    LastInv = Worksheets("Menu").Range("Z1").value

    NextInv = LastInv + 1

    Worksheets("Invoice").Range("H2").value = NextInv

    Worksheets("Menu").Range("Z1").value = NextInv

End Sub

Then, to save the invoice, something like this:

Sub SaveInvWithNewName()

    ' Copy Invoice to a new workbook	

    Worksheets("Invoice").Copy

    NewFN = Range("H2").value & ".xls"

    ActiveWorkbook.SaveAs NewFN

    ActiveWorkbook.Close

End Sub

Janice writes, looking for a way to change text that looks like numbers to real text. She imports data each day and since the key field in her lookup table contains numeric values, the lookup will not work. She says, I can get around this by formatting the column the same as the lookup, but then I have to edit, replace all the cells to get the format to take. For example, I will have to edit, replace 44 with 44; then edit, replace 55 with 55. This is the only way I can get it to work.

Is there any way to get the formats to take without edit, replacing? Or is there a way to edit, replace without changing a specific cell. I've tried blanks & spaces, but it says that nothing is found.

Yes, there are a lot of ways around this. The first approach is to simply change your VLOOKUP formula a little bit. If your VLOOKUP formula is normally something like this:
=VLOOKUP(A2,$AA1:$AB100,2,FALSE)
then add a 0 to the first term:
=VLOOKUP(A2+0,$AA1:$AB100,2,FALSE)

When A2 contains text that looks like a number, adding zero to the text field will cause Excel to evaluate the text 44 as 44.

The second approach is to use the =VALUE() function. This function will convert text that looks like a number to a number. Any application of this approach is generally made obsolete by the following trick, which is the best answer to your question.

The commonly accepted trick to solve this problem is as follows:
1. Find an out of the way cell and enter a 1 in that cell
2. Highlight the new cell and Ctrl+c to copy the cell
3. Highlight your range of text that looks like number in A2:A46
4. Edit - PasteSpecial. In the 2nd section of the PasteSpecial dialog, select Multiply. Click OK

All of your text will change to numbers. I suspect that the PasteSpecial - Multiply feature was designed to multiply ranges together, but someone discovered this use for changing text to numbers and is a real time saver.

A tip of the MrExcel cap and a free Salt Lake City 2002 Olympic calendar to Mark R. who wrote in with the other solution to this problem that works very well:
Highlight the entire column, select Data, Text to Columns, Finish.

This is excellent. A single mouse click to highlight the column the Alt+d e f, Three keystrokes and one mouse click. Excellent tip.

Dan asks I keep having a problem converting numbers into dates...I have the date 19960105 for example, but excel doesn't recognize it as a date without me having to put two slashes into it (i.e. 1996/01/05)...Is there any formula that I can use that will prevent me from having to put in all those slashes?

One option is to use an event handler, somewhat like the military time tip at http://www.mrexcel.com/tip029.shtml. Another method would be a formula. The Date() function allows you to specify a date by giving the Year, Month, Day. The following formula with parse your value and convert it to a date:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))

Two tips today, and the day is already young... Russell has a column of internal IP addresses and he want to sort them. The problem is that the .100. addresses are coming before the .22. addresses.

If Russell adds a new, temporary column to be used for sorting, he can use this new custom function IPForSort(). This code should be pasted into a code module in the workbook.

  

Public Function IPforSort(OrigVal)

    IPforSort = ""

    OrigVal = OrigVal & "."

    Bg = 1

    For i = 1 To Len(OrigVal)

        If Mid(OrigVal, i, 1) = "." Then

            IPforSort = IPforSort & Format(Mid(OrigVal, Bg, i - Bg), "000") & "."

            Bg = i + 1

        End If

    Next i

    IPforSort = Left(IPforSort, Len(IPforSort) - 1)

End Function

Ed wrote and asked about a simple macro: Do you have a macro that can add rows to a spreadsheet (at the bottom or
at cell pointer), then copy from the previous row and insert the data in the new row. And, finally delete data in certain cells.

Here is a macro that will insert a row above the cellpointer and copy the contents of the prior row. Let's assume your data extends from A to T, and that you want to delete the contents of the new row's cells H, K, and M

I'll use the keyword ActiveCell to make the macro work in relation to the cell pointer.

Sub AddRowForEd()

    ActiveCell.EntireRow.Insert

    Cells(ActiveCell.Row - 1, 1).Resize(1, 20).Copy _

        Destination:=Cells(ActiveCell.Row, 1)

    Cells(ActiveCell.Row, 8).Clear ' Column H

    Cells(ActiveCell.Row, 11).Clear ' Column K

    Cells(ActiveCell.Row, 13).Clear ' Column M

End Sub