Roger asks “We are racking our brains here trying to design a form in Excel that
automatically increments the number (i.e. 5600) by one each time the form is
opened. Is there a formula or macro that will accomplish this task?”

In the Workbook module (Or, if it’s a Userform, in the Initialize event of that Userform) put the following:

Private Sub Workbook_Open()
Sheet1.Range("A1") = Sheet1.Range("A1") + 1
End Sub

William asks “This is probably a breeze, but I'm stumped. I'm creating a tally sheet for my many day trades. One of the columns is "gain or loss". This is obviously established by simply establishing the difference between the "total invested" cell and the "net proceeds" cell. My problem is representing gains as positive numbers and losses as negative numbers. If "net proceeds" is greater than "total invested" then I made a profit. If I invested 10,000 and had net proceeds of 12,000 then there's a profit of 2,000. Yet if subtraction is used to establish the difference, the 2,000 will be represented as a negative amount, and vice-versa. The following is what I think I need to accomplish in the formula cell for "gain or loss" but I don't know how to write the formula:

If (total invested cell) > (net proceeds cell) then the difference is a negative number
If (total invested cell) < = (net proceeds cell) then the difference is a positive number

A solution would be tremendously appreciated.”

Assuming that the “Net Proceeds” are in Column A, and “Total Invested” is in Column B, try the following formula:

=A1 – B1

In your example, if A1 = 12,000 and B1 = 10,000 then the formula would show a POSITIVE 2,000. If Net Proceeds is smaller than Total Invested then it would be a NEGATIVE number.

Daniel asks

“14 20
12 25
10 35
8 50
4 65
I have a cell at let say E5 = to 30
the next number up is 35 and i want
to display the 10.
If E5 = to 55 than i want it to display
4
What is the formula for this?”

If the data is located in A2:B6, then use this formula:

=INDEX($A$2:$A$6,MATCH(E5,$B$2:$B$6)+1)

Ed asks “I wish to match the name of a worksheet tab with the header of a form
automatically. In other words, If I type the name of the form in a header (a
cell), the worksheet tab would somehow be linked (or ref) to the header, and
would change the name to match the header name.”
This requires some code in the Worksheet module (Or in the workbook module, to work for all sheets). Try the following:

WORKBOOK MODULE

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" Then Sh.Name = Target
End Sub

Dave asks: I have a worksheet with monthly columns of data in B through M. The total is in row 34. In column N, I entered a formula to calculate the percentage of the total for column B. I entered a formula of =B4/B34. Since I have about 25 cells I want to reference cell B34, when I try to copy the formula down to the next 24 cells holding the control key, it simply skips the same number of cells and divides the total by the wrong cell (not B34).

I solved this by placing dollar signs ($B$34) and then copying down, but when I try to copy everything and set up the next month, it copies $B$34 to the next month also. This is wrong because my new sales total is C34 not not B34.

Do you know how I can get around this?


You'll notice there are two dollars signs in $B$34. Each dollar sign holds one portion of the address as fixed or "absolute". The $34 says "always point to row 34". The $B says "always point to column B". Change you $B$34 to B$34 in the formula. You can then copy the formula, it will always point to row 34, but will point to new months as you copy the formula over.

Arthur asks “Hi, I've got to believe there's a way to keep the row of column heading
always visible on the screen as I scroll down through many deeper rows
and row 1 disappears.
I know I can print a new page always with a header row, but I'm just
concerned about having it visible on the screen while viewing rows too
deep to show row 1 headers (or may even column headers on row 5, etc.).”

Sure, for example, select B2, go to Window | Freeze Panes.
Now, scroll to the right, then scroll down, see what happens…

Gregory asks “If I have 11.5 in a cell how do I get this converted to 11 minutes and 30
seconds in another cell?”

Use this formula

=A1/(24*60)

Where A1 houses the 11.5. You may need to format this cell as mm:ss

Bob asks “I need to import data in a flat file on a daily basis, each file is an addition to the previous one. I have recorded a
macro that works fine for the first import, but as the destination is a named cell it only works once. How can I change
the macro so it writes the data to the active cell rather than a named one?.”

Change your recorded macro where it says something like

Range(“MyRange”)

To

ActiveCell

Jeremy asks “How do I get a macro to run automatically when the user opens a workbook?
Jeremy Kim”

Place something like this in the Workbook module

Private Sub Workbook_Open()
Call MyMacro
End Sub

Where “MyMacro” is the name of YOUR macro.

Deepak asks “Hi ,
I am trying to assign the current date to a cell A38 using =NOW() and then do a SaveAs by assigning the contents
of that cell as the file name for the file being saved. I am not being able to write the correct code for it .

ChDir "C:\Backup"
ActiveWorkbook.SaveAs Filename: = Range("A38").Text, FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False

In the above mentioned code all I need to do is to replace the filename by the contents of a cell A38 which will always contain the
current date. I would appreciate if you would please let me know the correct way to write this code. I tried to replace Filename with a few
things looking at tip025 in www.mrexcel.com in but it did not work.”

The problem arises when the format of the date includes the “/” character… try the following:

ActiveWorkbook.SaveAs Application.Substitute(Range(“A38”).Text,”/”,”-“)

Michael (Another one !) asks “Can I change a cell value without changing the formula?”

Well…. No. I mean, if you have in a cell

=1+2

The result will Always be 3. You can’t change the value to “4” without replacing or changing the formula.

Michael asks “Hi Mr. Excel,

I just want to know if there's a way (other than manually doing it through "F2") to delete decimal point in "amount" thus converting it to text. Example 2535.25 to 253525.”

Sure, select the range you want to “convert”. Go to Edit | Replace, put “.” (Without the quotes) in the “Find what” box, and leave the second one empty. Make sure that you select “Formulas” in the Look In combo.

Some other "basic" Excel tips to make your life easier:

Learn to use the Fill Handle (That little Black box that appears at the bottom right of the current selection). It has many "hidden" features... you use it by dragging it with the mouse. If you drag it with the right button you also have some cool features, like Fill formats, copy cells, use a geometric tendency, among some others.

The Page break preview is a powerful way of designing the way your pages will be printed. You can drag, erase, combine and insert page breaks where you want to !

You can setup the page configuration for multiple pages at once ! select them all (With Control or with Shift) and then go to File, Page Setup. All the changes you make there will apply to all selected sheets.

The Autofilter (Data | Filter | Autofilter) is a simple, but powerful tool for handling data base like tables.

There's a difference between Clear and Delete. The first one only removes the contents, formatting or "everything" that APPEARS on the cell. The second one, on the other hand, removes the cell (Or row, or column) for the sheet Entirely ! this is the option I use most, in fact, almost never I use the first one !

Ok, enough for today...

I thought about putting a small list of Keyboard shortcuts to make your life a little easier:

Control + "C": Copy
Control + "X": Cut
Control + "V": Paste

F2: Edit current cell (Makes cell references appear in colors for easy editing)
F5: Go to
F11: "Instant" chart.

Shift + F3: Paste function wizard
Control + F3: Define Name

Control + "+": Insert Cells, Rows, Columns. Depends on the current selection
Control + "-": Delete Cells, Rows, Columns. Depends on the current selection

Shift + Space: Select entire row for current area (Hard to explain with words, try it out to see what it does)
Control + Space: Select entire column for current area

Control + "!" (Or Control + Shift + "1"): Format as Number with 2 decimals
Control + "$" (Or Control + Shift + "4"): Format as Currency
Control + "%" (Or Control + Shift + "5"): Format as Percentage
Control + "/" (Or Control + Shift + "7"): Format as Scientific

Control + "&" (Or Control + Shift + "6"): Places exterior (thin) border around current selection

While I am sure your favorite web site for Excel help is the one you are reading right now, I occassionally need help in Word or Access and always wished that someone would create a MrWord.com or a MrAccess.com.

DreamBoat filled the Word void with TheWordExpert.com

Now, there is a site devoted to Access. Check them out at www.technoweenies.ca

Conditional formatting allows for 3 conditions. By setting up conditions correctly (least to most restrictive), you can really allow for 4 conditions when the default formatting of the cell is considered. For example, if you want to fill cells less than or equal to -25 red, between 0 and -25 yellow, 0 to 25 green, and over 25 black, this can be achieved by doing the following:

Select your range of cells.
Select conditional formatting from the Format menu.

From the drop down menu, set condition 1 to “Cell Value Is” and “Less than or Equal to” and put in the value -25. Now press the format button and choose red from the patterns tab and click Okay.
Click add, and repeat the above steps changing the value to 0 and a color to yellow for your second condition. Click add again, and repeat with a value of 25 and a green color. Click okay to exit the conditional formatting dialog box.

Finally, while your range is still highlighted, choose a black fill from the color palette, and your range is now pseudo-conditionally formatted to cover your four cases.

IML

First off, a special nod to MrExcel for giving a hack like me a shot or two as guest daily tipster. Let me preface this by saying most everything I learned about excel came from this great site, so don’t be surprised (or accuse me of theft) if things look familiar. To the tip:

Vlookup is a powerful tool. Sometimes, if an exact match is not found, a value other than the standard #N/A is desirable. For example, if your look up range (table_array) is A1:C10, the formula

VLOOKUP("cat",$A$1:$C$10,3,FALSE)

will return #N/A if cat is not found in A1:A10. If instead you wanted to return the phrase “No Match”, the following formula is often suggested:

=IF(ISNA(VLOOKUP("cat",$A$1:$C$10,3,FALSE)),"no match",VLOOKUP("cat",$A$1:$C$10,3,FALSE))

This functions properly, but at the expense of having to lookup your value twice. This can be improved by the following:

=IF(COUNTIF($A$1:$A$10,"cat")>0,VLOOKUP("cat",$A$1:$C$10,3,FALSE),"no match")

or with even less carpal tunnel risk by recognizing excel treats zero and “False” identically:

=IF(COUNTIF($A$1:$A$10,"cat"),VLOOKUP("cat",$A$1:$C$10,3,0),"no match")

IML

Ruth asks: I need to convert an Excel file into Salesforce.com. The file was given to me in all capital letters. How do I change the case globally so that it
is properly formatted with Ruth (capital first letter and the rest in small case) so that I do not have to go in and manually retype everything? (500+ lines)?

The =PROPER() function will do this for most records.

Let's say your data is in A2:G600
Over in blank columns off to the right, enter a formula of =PROPER(A2)
Copy this formula from, say H2 to H2:N600. Make the shape of this range of formulas match the shape of your original range. If you original data has 7 columns, make this data have 7 columns.

You then have to change the formulas to values using these steps:
Highlight your range of formulas - H2:N600
Edit > Copy
Edit > Paste Special > Click Values > Click OK

NOTE: you need to carefully look through the results. Excel does not handle McDonald correctly - it will set it to Mcdonald. Check the "Mc" and "Van" records and manually correct these.