Q. Wants to add numbers based on the text in
the column next to them . . .
A. From Alan Beban beban@pacbell.net
A Sum+If formula entered as an array will do it. Even though that may sound imposing, it's really fairly simple. I put the two columns in A1:B9; in A10 I put "leaver total", in A11 "joiner total". In B10, type =SUM(IF(($A$1:$A$9="leaver"),$B$1:$B$9,0)) and enter it by pressing Ctrl+Shift+Enter (instead of just Enter); do the same in B11 with =SUM(IF(($A$1:$A$9="joiner"),$B$1:$B$9,0)).
Q. Would like automatically use the full path
in the footer of Excel files.
A.
Sub Path_Footer()
With ActiveSheet.PageSetup
.LeftFooter=ActiveWorkbook.Path & Application.PathSeparator
&_
ActiveWorkbook.Name
.CenterFooter=""
.RightFooter=""
.FooterMargin=Application.InchesToPoints(0.35)
End With
End Sub
This procedure will add the full path to the
left footer of every sheet in the active workbook. Note that it
is enhanced by formatting the text to your desired font and font
size. It then shows you a MsgBox with the full path that was
added. I made the macro available by putting it in my personal
macro workbook and adding it to the bottom of the edit menu.
This is as "automatic" as it gets. Caveat: If you
relocate or change the file name, you will need to re-run the
macro to change the footer.
Sub FullPathFooter()
' From Rick Teale rteale@s2.sonnet.com
With Application
.StatusBar = "Adding Full Path Name to Left Footer."
.DisplayStatusBar = True
End With
For Each sht In ActiveWorkbook.Sheets
Ampersand = Chr(38)
Quote = Chr(34)
FooterFormat = "Comic Sans MS"
FooterTextSize = "8"
FooterText = ActiveWorkbook.FullName
Formatting = Ampersand & Quote & FooterFormat & Quote
& Ampersand &
FooterTextSize
Footer = Formatting & FooterText
sht.PageSetup.LeftFooter = Footer
Next sht
Application.StatusBar = False
MsgBox FooterText & Chr(10) & Chr(10) & "added
to left footer of each worksheet."
End Sub
Q. Im tring to add a note to a current cell
with my sheet protected. I know I could go to the Insert, note at
the toolbar, but what I would like to do is record a
macro,unprotect the sheet, goto add note,then pause my macro
,type my note,then continue my macro.If that can't be done, could
anyone help me with writing a macro to assign my selected note
into a current cell?
A. From Beth
Here is a sample macro for you. It unprotects
the sheet, uses the InputBox to capture the user text into a
variable 'x'. Then inserts the text from variable 'x' into the
cell and protects the sheet again.
Sub AddNote()
ActiveSheet.Unprotect
x = InputBox("Enter Note:")
ActiveCell.NoteText Text:=x, Start:=1
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios _
:=True
End Sub
A. From: DonnaPayne@msn.com (Donna Payne)
Select the cells that are to have the
conditional format applied and choose the Format menu, Number,
Custom commands. You can have up to three colors of conditional
formatting conditions plus text. Each condition is separated by a
semicolon. You can copy the custom format below and paste it in
if you'd like. It will work for you.
[Red][>=90]l;[Black];[Black]@
To better explain this however, I'll present another example:
[>1000][green];[<50][blue];[red]
Here all values greater than 1000 would be green, values less
than 50
are blue, otherwise make the value red.
Q. In one column there is a number of seconds (e.g. 683), in the next is required the number of minutes (e.g. 11 mins 23 secs) - How ?
A. From "Lawrence E. Durbin" lnusdva.dz6tc7@eds.com
Try :
=QUOTIENT(A5,60) & " m " & MOD(A5,60) &
" s"
Where A5 is the cell containing "683" in your example
.... (you may have to install the "Analysis Toolpak"
Add-in to get the function).
A. From Stuart Zussman szussman@pacbell.net
Use the formula x^(1/y) for the yth root of x
Q. How to display a graph whose data is hidden.
A. From Shane Devenshire
1. Double click the graph to activate it
2. Choose Tools, Options, and on the Chart tab uncheck Plot
Visible Cells Only
Displaying the current sheet name in a cell
A. From Bill Manville
=CELL("filename",$A$1)
will give you more than just the sheet name.
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
will give you just the sheet name.
Finding the 2nd Smallest value in a list
Q. Need an easy way to calculate a second (or third, etc.) minimum/maximum value for a given range of values in a row.
A. From Alan Beban beban@pacbell.net
=SMALL(A1:G1,2) will return the second smallest
value in the range. The only thing to keep in mind is that =SMALL({1,2,4,2},3)
returns 2
=LARGE(A1:G1,2) will return the second largest value.
Q. A sheet of data was being imported into
access from excell but the dates were importing wrong values. eg;
(Using Aust d/m/y format )
26/1/90 <= 27/4/94
7/7/90 <= 8/7/94
A. From Michael J. B
1- 99.9% of the spreadsheets software programs
give to the users two date systems
2-Most likely one of the two files (client or source were set
into the "1904 date system"
3-This is why you are getting such discrepancy between the two
dates... [quoting] "the difference of 4 years between the 2 dates..."
Solution [probable]=
1-Select the command TOOLS from your destination worksheet (i.e.
the release where you will copy or import all the spreadsheet
files...)
2-then move the pointer to TOOLS - Calculation
3-Under the title "Workbook Options" check the option
"1904 date system"(Normally most of the worksheets are
created WITHOUT this option checked... try then either ways when importing,
I assume it will do the job...).
The function MINVERSE doesn't allow array sizes greater than 52x52.
Q. Want to start entering data into B5 and when tab or enter
go straight to E11 then to A14 etc. All the cells are protected but
those.
A. From Fred Tully fredtull@compusmart.ab.ca
,Hold down control,select E11,.....,B5(the first is last) and
give it a name(insert,name, define....etc)
Click the arrow Beside the cell name
Select the name
If you want a button, do a macro to call the name
Opening a file with a date name
Q. Want a macro that opens a file with the format NAMEmmdd.xls
where mmdd is today's month & date. Use the =today() to allow
a macro to open today's file automatically.
> How would I modify my macro to get the file.
A. From Mike Field
Dim NameDate
Let NameDate = [TEXT(TODAY(),"mmdd")]
ChDir "O:\operations\CASH FORECASTING\CASH-BAL"
Workbooks.Open
Filename:="O:\operations\CASHFORECASTING\CASH-BAL\NAME"
& NameDate &
".xls"
The variable NameDate will return -- mmdd -- this is placed in
your
file open statement.
A.From Martin Richardson
1. From within Excel - INSERT / OBJECT /
Microsoft Wordart 2.0 (or bring the slanting A tool into the
toolbar from the Insert category)
2. In Wordart type in the text that you want
3. In Wordart, use the third tool in from the right to make your
text have a clear fill
4. Using the far right tool in Wordart to give the outline of
your text a hairline rim and colour it silver (note the correct
way of spelling colour !)
5. Click away to return to Excel.
6. Expand the object as desired (using the Shift key whilst
dragging one of the corner handles to keep the proportion)
7. Hit the object with your alternate mouse button and select
Format Object. Then kill the border and fill.
8. Finally send the object to the background.
P.S. From Carl Decker, Excel 7.0 can bring in a background to the page by selecting: format, sheet, background and then bring in a *.wmf file made from PowerPoint. or a bmp. (just does not know how to print the watermark)
A. From (Donna Payne) DonnaPayne@msn.com
Sub SelectEveryNthRowInSelection()
Dim iNth As Long
Dim i As Long
Dim oNth as Range
iNth = CInt(Application.InputBox(Prompt:="Enter the nth row
value:', _
Title:="Select Every Nth", Default:=2, Type:=1))
If iNth = 0 Then Exit Sub
For i = 1 To Selection.Rows.Count Step iNth
If oNth Is Nothing Then
Set oNth = Selection.Rows(1)
Else
Set oNth = Application.Union(oNth, Selection.Rows(i))
End If
Next i
oNth.Select
End Sub
Q. Have about 3000 file names that look like
this:
ABCLAO-1
BOSPAO-5
COA-9
Need to eliminate the -1, -5, and -9
A. From Shane Devenshire
Suppose the names are in cells A1:A3000
1. Place your cursor in cell A1 and press Shift+Ctrl+down arrow
2. Choose Data, Text to Columns, Delimited and press Next
3. Set the Delimiter to Other and enter - as the delimiter and
press Next
4. Click in the second column of the data display and choose Do
Not Import
Column
5. Make sure the Destination is A1 and then press Finish.
Ctrl ` switches between Value view and Formula view. This allows the formula to be printed. You could also write a short macro to switch to Formula view when an error in the formula is encountered :
Sub Formula_Flipper()
On Error GoTo EndMacro
ActiveWindow.DisplayFormulas = Not ActiveWindow.DisplayFormulas
EndMacro:
End Sub
Q. How can I automatically pull out the first 3 digits from the phone number field and paste them into a separate field set
A. From Alan Beban beban@pacbell.net
Assuming your phone numbers are in A1:A100, enter the following
in B1 (or C1 or D1 or . . .)
=LEFT(TRIM(A1),3)
and copy it down through Row 100
Or (from Stephen Bullen) Just use
=LEFT(A1,3)
Excel knows to convert the number to text and take the left 3
digits
Under the 'Tools' menu, select 'Options ...' and deselect the 'Zero Values' under 'View'. Another option is to format required cells as #.
A. From bunglesbee@aol.com
' This macro expects the column B to contain data until the
last cell in the data range. Be sure your data is sorted by the item
you are checking for duplication. Column B is used to compare
entries in the list.
Sub RemoveDups2()
Range("B9").Select
' start in beginning of range and go through each row until a
blank
' is found
mcellValue1 = ActiveCell.Value
ActiveCell.Offset(1, 0).Range("A1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = mcellValue1 Then
Selection.EntireRow.Delete
End If
mcellValue1 = ActiveCell.Value
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
End Sub
A. From b.grant@fcollins.com.au (Bruce Grant)
If you place the mouse pointer just above the vertical scroll
bar on
the right side of the screen, you will notice that the pointer
becomes
a double-ended arrow. Drag this down to the position you wish to
lock
into position. Incidentally, this gives a similar solution to
that
offered in the other replies in your posting with the benefit
that the
locked section gains its own scroll bar. To remove this lock
simply
double click the line across your screen or drag it all the way
to the
top of the worksheet.
A. From Mike Adair...
Sub OpenBook()
'compliments of: Mike Adair
On Error Resume Next
If IsError(Workbooks("Calendar.xls").Name) Then
Workbooks.Open "C:\msoffice\excel\xlstart\calendar.xls"
Else
Workbooks("Calendar.xls").Activate
End If
On Error GoTo 0
End Sub
A. From Carl Decker
Selection.EntireRow.Select
Selection.Delete Shift:=xlUp
A. From Rob Bovey
VBA will not allow you to print while a custom dialog is
showing. What you need to do is have your Print button formatted to
dismiss the dialog and also assign it to a procedure that sets a
flag. You can then use this flag to decide whether to print after
the dialog has disappeared.
The macros below demonstrate this method. Assign your print
button to the SetFlag macro and also format it to dismiss the dialog
(right-click on the button and choose <Format Object><Control>
Dismiss). Then use the ShowDialog macro to show the dialog.
' The following two macros demonstrate how to print from a
dialog
Dim Flag As Boolean
Sub ShowDialog()
Flag = False
DialogSheets("PrintDialog").Show
If Flag Then Sheets("Sheet1").PrintOut
End Sub
Sub SetFlag() ' This macro is assigned to the print button
Flag = True
End Sub
A. From Alan Beban beban@pacbell.net
Assuming the locked cell is on a sheet named "MySheet",
put the first line immediately before the line where you write to the
cell, the second line immediately after it:
Sheets("MySheet").Unprotect
Sheets("MySheet").Protect
The second line will relock the cell, and it will also lock any
objects on the sheet. If the latter is not what you want, then look
up the Protect Method in the On-Line help to see what arguments
you want to supply with it.