Jason asks "I am trying to make a cell reference that always automatically adds a * to
the beginning and end of the data.

For example, I put 12345 into cell A1. Then in cell B1, I want it to
automatically say *12345*

Ive tried doing a formula in cell B1 like ="*"=B1"*" or =("*"(=B1)"*") but
i cant think or a way to do it. The reason im trying to do this is because
im trying to make a bar code and it needs a * at the beginning and end of a
number for it to work. Please help"

You were close. Try with

="*"&A1&"*"

in B1.

Chris asks "I am using the IF function, but I want the Value if True and the Value if False to be applied to a cell different from the cell that contains the actual IF function. For example, I tried someting like this:

Cell A1 reads the following: =IF(A2=5,A3="YES",A3="NO")

However, Excel doesn' t put any data in cell A3. Instead it puts "FALSE" in cell A! regardless of what is in cell A2."

Functions can only return a value, they can not modify their "environment". So, the "workaround" for this problem would be to put this formula:

=IF(A2=5,"YES","NO")

in A3 instead of A1.

Kathy asks "I am trying to create a macro for a Button to: exit a worksheet, without saving (it is a Read-only template file), and close Excel...the end users are not comfortable with PC use, so the fewer choices left to them the better...

I'm relatively new at macros and am finding it difficult to locate complete help. Trying to record this resulted in knocking me out of the workbook."

Try this code:

Sub CloseExcel()
ActiveWorkbook.Saved = True
Application.Quit
End Sub

You can change the

ActiveWorkbook.Saved = True

with

ActiveWorkbook.Close SaveChanges:=False

Anupam asks "I am trying to create and Name a sheet from within a Excel Macro. If I use the function Sheets.Add it just names it something like Sheet2 etc naming it greater then the last sheet created. So I cannot access this sheet from within the same macro. Is there a way to Create and name a worksheet at the same time."

Well, the first thing, is use Objects. This is a recommendation, you can live without using them, but, life becomes so much easier when you do...

Just like this:

Dim WS as Worksheet
Set WS = Sheets.Add

You don't have to know where is it located, what's it's name, you just refer to it as WS.

If you still want to do this the "old fashioned" way, try this:

Sheets.Add.Name = "Test"

Hello Everyone. I am going to ask you to bookmark a hot new site today.

Many times, I have jokingly suggested that a questioner should seek out MrAccess.com or MrWord.com or MrOutlook.com only to lament that no one has taken up that role yet.

Well, I am glad to announce that an old friend has taken on the role of offering expertise in MIcrosoft Word. DreamBoat's new web site devoted to Microsoft Word is now live at http://www.thewordexpert.com/index.htm

Be sure to check out DreamBoat's great web site for all of your Word questions.

Tony asks "I am having a problem modifying a worksheet that I've had no problems with in the past. I wanted to insert two new columns to the spreadsheet but I couldn't due to the intersecting point of a merged cell. No big deal, I will just "un-merge" them using the "Format Cells" option. I tried to remove the check mark from the "Merge Cells" box but the option was unavailable. The box was gray and could not be adjusted. Do you have any advice?"

When you have to insert a column and there are some merged cells, DO NOT select the entire column, instead, just select ONE NON-MERGED cell, then go to Edit, Insert, and select "Entire Column". That should work.

Richard asks "mrexcel, I need a formula to add the 9 highest valued cells out of 11 cells. I have a motorcycle racer database set up on Excel 97 and during the series I will count a riders top 9 races (they have to run 8 races to be eligible).
Therefore if a rider races 8 races I will just add all 8 races.
If a rider races 9 races I will also add all 9 races.
If a rider races 10 races I will add 9 races and "drop" there worst race (lowest value).
If a rider races all 11 races I will add the highest 9 valued races and "drop" the worst 2. "

If your races are in A1:A11, try with this formula

=IF(COUNT(A1:A11)<8,0,SUM(A1:A11)-(COUNT(A1:A11)>9)*SMALL(A1:A11,1)-(COUNT(A1:A11)>10)*SMALL(A1:A11,2))

Angel asks "I am a teacher, and I use excel to do my grading, but I still have to manually change the number grades to letters because I have not figured out what to do yet. Please help. Examples of what I am talking about.

John D = 83 which is a B-
John D2= 85 B+

and so on a so forth."

The solution is simple. Create a Table that looks something like this:
0 F
60 D
70 C
80 B
90 A

Where you put the LOWER limit of each range. For example, F goes from 0 to 59, so you put a 0 and next to it, an "F". After this, select this Table, go to the Name Box (To the left of the formula bar) and type GRADES

Now, if your grades are in A2 use this formula (Which you can drag down or across)

=VLOOKUP(A2,GRADES,2)

Rene asks "I would like to know how I could actually remove the colon from the time value without it turning to decimal. example when the time value is entered 23:59, I would like it to automatically change to 2359"

Strange question... I always seem to find questions about going the other way. Anyway, if your time is in A1, use this formula:

=TEXT(A1,"hhmm")

Louise asks "How can I insert our logo (bitmap) into Excel as a footer to appear on every sheet?"

Unfortunately, this is only possible "directly" in Excel XP. Before that, you would have to "simulate" a footer by placing the image in a row at the end of each page.

Paul asks "Please help. I work closely with comparative lists and need to learn how to either identify duplicate cells or even better filter out duplicates. Can you help? "

Sure, just select the range where the duplicates are. Go to data, Advanced Filter, select "Copy to new location", leave the "Critiera" field empty, and make sure you have chekced "Unique Entries Only".

Angie asks "We do Biomedical Research and we use Excel as our primary data editor. We need to enter into a cell things like 1-2 and 6-5. Yet, when we enter that, it turns it into a date like Jan-2 and Jun-5. We need to turn this off, and turning off the Autocomplete function does not do it. Have any ideas? Thanks!"

Two options, format the Column (Or range) where you will input the data as TEXT, Before inputting the data. Second, input the text with a ' starting, like this

'1-2

and Excel won't translate it.

Roy asks "Can you freeze rows and columns at the same time. Say for instance you
have the following scenario:

Cell B1 Fname
Cell C1 Mname
Cell D1 Lname
Cell E1 Address

Cell A2 Paid
Cell A3 Paid By
Cell A4 Date

Can you freeze this heading going across and down at the same time where
only the data scrolls. Which ever direction I scroll I will lose one set
of the heading. They both will not stay on the screen all the time. NO
ONE ELSE CAN FIGURE IT OUT. "

Select B2, and go to Window, Freeze Panes... Row 1 and Column A should be frozen

Rob asks "I have programed a cell to round the time to the nearest quarter hour. Such as, 8 hours 14 minutes would show as 8:15:00. Now I want to program the cell to show that 8:15:00 as 8.25. What is the formula for that?"

If you have 8:15:00 in A1, use this formula (And format as General, or as Number with 2 decimals)

=A1*24

David asks "How can i link something like a message box which works like a hyperlink. I would like something which the user gets
asked a question and yes leads to macro and no does nothing."

Use a MsgBox

If MsgBox("My Question", vbYesNo,"Title") = vbYes then
'Do something
Else
'Don't do anything
End If

Anthony asks "When selecting the '0 -xlUnlockedCells' properity in the EnableSelection
window only the unlocked cells can be selected when the workbook is
protected. However, when the workbook is Saved, Closed and then re-opened
the EnableSelection defaults back to '0 -xlNoRestrictions' how can I stop
this happening

Regards"

Well, this can be solved using some events. One, using the Workbook_Open event, or two, using the Sheet_Activate event, like this:

In the workbook Module

Private Sub Workbook_Open()
Sheets("Sheet1").EnableSelection = xlUnlockedCells
End Sub

or in the Sheet's Module

Private Sub Worksheet_Activate()
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

One final thing, xlUnlockedCells equals 1, not 0. 0 is xlNoRestrictions and -4142 is xlNoSelection.

Mike asks "In my workbook, if a control checkbox is not checked then I would like to hide specific rows and columns automatically with VBA. How is this done?

Thank you"

Being a Control checkbox, you would have code that looks like this:

Private Sub CheckBox1_Click()
ActiveCell.Select
Rows(15).Hidden = CheckBox1
End Sub

The first part is to avoid an error IF you are using Excel 97. The second line is an "improved" IF, it could be made like this:

If CheckBox1 = True then
Rows(15).Hidden = True
Else
Rows(15).Hidden = False
End If

But why do all the extra typing ?

Travis asks "I am trying to format some text into separate columns. The text is in one
column as follows:


Column A
Company Name A
PO BOX 1
NEWARK NJ 07101

Company Name B
PO BOX 2
1400 SANS SOUCI PARKWAY
WILKES-BARRE PA 18703

Company Name C
PO BOX 3
CHICAGO IL 60694

Company Name D
PO BOX 4
CLEARFIELD UT 84015

Company Name E
PO BOX 5
OGDEN UT 84401

Company Name F
PO BOX 6
BALTIMORE MD 21264-4351



When I formatted the test to excel I made this column as illustrated above. I would like to know of a formula to which I can make the data appear on one
row rather than in multiple rows for on company the formula should result if giving me that following in one row but in different columns:

All on the same ROW

Column A                       Column B          Column C
Company Name A         PO BOX 1          NEWARK NJ 07101

I have use vlookups and hlookups and pivot tables but I can’t figure out how to get the date if a row-by-row way.

I have been able to get company names to appear row-by-row in one column by sorting and I figured that I could use a combination of a vlookup with a
hlookup to give me the results I was looking for but I do not know the proper formula to nest in the ether lookup. I’v also tested some DSUM function but none are working for me? So please let me know what or how to fix my problem…. "

I think this can be easily achivied using a macro. Try this one.

Sub CopyAcross()
Dim i As Long
Dim NRow As Long
Dim LastRow As Long
NRow = 2        'First Row TO COPY, change this if you want
i = 2         'First Row where the data appears, I assumed it started in A2

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

While i <= LastRow
Range(Cells(i, 1), Cells(i, 1).End(xlDown)).Copy
Cells(NRow, 2).PasteSpecial Transpose:=True         'In here, I'm copying to Column 2, Next availabe Row. You can change the 2 if you want.
NRow = NRow + 1
i = Cells(i, 1).End(xlDown).End(xlDown).Row
Wend
End Sub

Paul asks "I have a number of columns of text data in my Excel database which contain
boys and girls names some of which are repeated many times, I have to return
a summary of the columns to a different spreadsheet. The information that
should appear on the summary sheet is the the name and the amount of times
it appears in the data base columns. the name should only appear once in the
summary sheet and along side it in another column is a count of the number
of times it appears in the data base column. Can you help with the
solution. "

This is the PERFECT job for a VERY FAST AND EASY Pivot Table. Let's assume that your names are in Column A, with the heading in A1. Select this column (A1:A100 if you have 99 names), go to Data, Pivot Tables, make sure that the origin range is set up correctly (Should appear Sheet1!A1:A100 or similar), and in the table definition, drag the heading to the ROW fields, AND to the DATA fields. You should see something like

[ROW]       [DATA]
Names       Count of Names

The Pivot Table should only show unique names, and the count of the times each name appeared on the list.

Meredith asks "I am writing a macro that will assign a check to the active cell i have
chosen. My code is as follows:

ActiveCell.FormulaR1C1 = "x"

I want to know how to change the font from arial to wingdings in my macro.

Can you help me figure out the code to format my active cell with a
specific font and assign my "*" symbol???

Thank you for your help."

Ranges have a Font object, that handle all the things that you can do with it, that is, make it bold, change its size and change the font, among others. All you have to do is:

ActiveCell.Font.Name = "Wingdings"

David asks "Hi I have to design a project and i'm stuck on a bit which is designing a macro to allow the user to input a number, but the problem i'm facing is setting a validation which would only accept numbers between 1-10. I tried to set a validation on the specific cells but the problem with the inputbox is that even if the cell has been set to only accept a number it would accept anything. Can you please help because i can't really carry on with my project until this is done."

This macro will continue to ask the user for a number between 1 and 10 or will stop if he/she cancels.

Sub Test()
Dim MyAns As Variant

Ask:
MyAns = Application.InputBox("Enter a number between 1 and 10", Type:=1)
If MyAns = False Then Exit Sub
If MyAns < 1 Or MyAns > 10 Then GoTo Ask:
MsgBox MyAns, vbInformation, "Good Number"

End Sub

Extracted from MrExcel.com Message Board

Jen asks "How can I make sure Excel opens my file maximized ?, I need this for visibility purposes"

To make sure of this, right click in the little workbook (Next to File menu), and choose, View Code.

You should see this, and the cursor standing between those lines.

Private Sub Workbook_Open()

End Sub

Put this code there.

Application.WindowState =xlMaximized

Extracted from MrExcel.com Message Board

Mike asks "Is there any way I can simply remove certain characters from a text string. For example, I have a post code BN19 4GX and I want to remove all numeric charaters to leave BN GX."

This is easier using VBA.

Go to the VB Editor (Alt + F11), go to Insert, Module. Paste this code in there.


Function RemoveNumeric(Rng As String) As String
Dim Tmp As String
Dim i As Integer

Tmp = Rng
For i = 0 To 9
Tmp = Application.Substitute(Tmp, i, "")
Next i
RemoveNumeric = Tmp
End Function


Now, back in Excel, if your initial text is in A1, put this formula where you want the result

=RemoveNumeric(A1)

and you'll get 'BN GX'. The function removes all numeric characters, that is, {0,1,2,3,4,5,6,7,8,9}, but can be adapted to substitute something else.

Matthew asks "Are there any hotkeys for switching between spreadsheet to spreadsheet?"

Sure, a couple listed below:

• Control Tab or Control Shift Tab: Switch between windows (Or workbooks)
  
• Control Pg Down or Control Pg Up: Roll between worksheets in a Workbook.
  

Tim asks: My colleagues can use the XIRR function, but it does not appear to be available in my version of Excel.

XIRR is one of the financial and engineering functions available in the Analysis Toolpack. To enable it, do the following steps:

• Tools > Add-Ins...
  
• Check the selection for Analysis Toolpack
  
• Click OK
  

From that point on, XIRR will be available to you. If Analysis Toolpack is not in the list of available Add-Ins, then someone did a custom or minimal install of your Excel. Get the install CD's, do a custom install, and be sure to check the Analysis Toolpack during install.

"S. M." writes I am a student. I just failed my Excel Exam. At college, we use Excel 97. The exam was set on Excel 2000. Could you tell me if this is why I failed?"

I could follow your theory if the situation were reversed. But, anything that you know how to do in Excel 97 should have translated directly to Excel 2000. Microsoft is pretty good about making sure that features from one version work in the next version. The one major exception that I can think of is that Excel 97 would run Lotus 1-2-3 macros and Excel 2000 would not. So, unless you solutions to the exam revolved around Lotus Macros, you should have been able to apply Excel 97 knowledge to Excel 2000.

As an aside, for any readers who are sticking with Excel 97 because they have a large supply of Lotus 1-2-3 macros that are still running in Excel 97, MrExcel.com Consulting can rewrite those macros in Excel VBA.

Murray asks "I want to keep a running total of the numbers I put in a column. For example in col A I have 12 15 12 16 22 14 11 etc. In col B I want the total of 12+15 then 12+15+12 then 12+15+12+16.......12+15+12+16+22+14+11=99. I tried using the E(sum sign) but I would have to repeat each time I entered a number in Col A. I would like to be able to copy the formula in COL B as far as the worksheet allows and as numbers are entered in COL A the total will show "as I go".

If the data starts in Row 2, then put this formula in B2 and drag down !

=SUM($A$2:$A2)