Problem with code
I have the following code below.
I have many more textboxes but this code has been made short.
The data which the userform gets its information is on two worksheets:
Sheet1= customers
Sheet5= cuatomers2
The problem I am having is that it will not recall, to the textboxes, the
information in "Sheet5".
I hope I have explained it, any help would be grateful.
Option Explicit
Private Sub CommandButton1_Click()
Dim LastRow As Object
Application.EnableEvents = False
Set LastRow = Sheet2.Range("a100").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
If vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
ComboBox1.Text = ""
ComboBox2.Text = ""
Else
End If
Set LastRow = Sheet5.Range("a100").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox3.Text
LastRow.Offset(1, 1).Value = TextBox4.Text
MsgBox "Do you want to enter another record?", vbYesNo
If vbYes Then
TextBox3.Text = ""
TextBox4.Text = ""
ComboBox3.Text = ""
ComboBox4.Text = ""
TextBox1.SetFocus
Application.EnableEvents = True
Call CommandButton99_Click
Else
End If
End Sub
Private Sub CommandButton2_Click()
Dim FoundCell As Range
Application.EnableEvents = False
If Me.ComboBox1.ListIndex = -1 Then
'nothing filled in
Beep
Exit Sub
End If
With Worksheets("customers").Range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
After:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
End With
If FoundCell Is Nothing Then
'this shouldn't happen!
Beep
Else
Me.TextBox1.Value = FoundCell.Offset(0, 0).Value
If IsDate(FoundCell.Offset(0, 1).Value) Then
Me.TextBox2.Value = Format(FoundCell.Offset(0, 1).Value, "dd-mmm-yy")
Else
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
End If
Me.ComboBox1.Value = FoundCell.Offset(0, 9).Value
Me.ComboBox2.Value = FoundCell.Offset(0, 13).Value
End If
With Worksheets("customers2").Range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
After:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
End With
If FoundCell Is Nothing Then
'this shouldn't happen!
Beep
Else
Me.TextBox3.Value = FoundCell.Offset(0, 2).Value
If IsDate(FoundCell.Offset(0, 1).Value) Then
Me.TextBox4.Value = Format(FoundCell.Offset(0, 1).Value, "dd-mmm-yy")
Else
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
End If
Me.ComboBox3.Value = FoundCell.Offset(0, 9).Value
Me.ComboBox4.Value = FoundCell.Offset(0, 13).Value
Application.EnableEvents = True
End If
End Sub
Private Sub CommandButton99_Click()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet
For Each wks In Worksheets(Array("customers", "customers2"))
With wks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = FirstRow To LastRow Step 1
If Application.CountIf(.Range("a1").EntireColumn, _
.Cells(iRow, "A").Value) > 1 Then
'it's a duplicate
.Rows(iRow).Delete
End If
Next iRow
End With
Next wks
Unload Me
End Sub
Private Sub CommandButton3_Click()
' keybd_event VK_SNAPSHOT, 0, 0, 0
DoEvents
keybd_event VK_LMENU, 0, _
KEYEVENTF_EXTENDEDKEY, 0 ' key down
keybd_event VK_SNAPSHOT, 0, _
KEYEVENTF_EXTENDEDKEY, 0
keybd_event VK_SNAPSHOT, 0, _
KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0
keybd_event VK_LMENU, 0, _
KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0
DoEvents
Workbooks.Add
Application.Wait Now + TimeValue("00:00:01")
ActiveSheet.PasteSpecial Format:="Bitmap", _
Link:=False, DisplayAsIcon:=False
ActiveSheet.Range("A1").Select
ActiveSheet.PageSetup.Orientation = xlLandscape
ActiveSheet.PageSetup.Zoom = 80
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWorkbook.Close False
End Sub
Private Sub CommandButton4_Click()
UserForm7.Show
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Call CommandButton99_Click
End If
End Sub
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.Value = UCase(Me.TextBox1.Value)
End Sub
Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox2.Value = Format(TextBox2.Value, "dd-mmm-yy")
End Sub
Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.Value = UCase(Me.TextBox1.Value)
End Sub
Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox2.Value = Format(TextBox2.Value, "dd-mmm-yy")
End Sub
--
kk Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770321
Index vs %
What's the difference between an index and percentage growth/decline?
Example: Last year's volume was 158 vs 2006's volume of 97. If I
divide 158 / 97 I get 162.9. Isn't that the same as 62.9%? Why would I
use one over the other and if I decide I don't want it to read 162.9,
just 62.9, how do I get that to happen?
Thanks. Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770320
Calender Control
I have added Calender Control 10.0 as an add-in. I want to allow the user to
use a calender to select a date for input via Inputbox. Once the date is
selected and entered, a macro is going to use the date.
I have no clue how to activate/use the calender control. How is this
accomplished?
Thanks,
Les Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770312
last non-empty cells
is there a formula for displaying the last non-empty cell or the last used
cell in a column or row? if there is kindly notify me at
donald_dick13@yahoo.com. i needed it badly. Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770306
copying conditional formatting
Hi,
I have a row of 5 cells. I want to format them so that when two of the 5
cells are >0 the whole set of cells change color. I used the countif function
to do this and it works fine.
I now want to copy this conditional format for all 5 cells down the other 50
rows. I have tried the usual paste, paste special, but these do not work.
Any help would be appreciated
Thank you
David Williams Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770304
Searching in 'variable' worksheets
Hi,
I'm a quite amatuer in Excel so I need a help in the following:
I developed a spreadsheet with several worksheets named each by months of
year. Each monthly sheet contains datas in same structures - they are daily
production figures, losses etc.
Now I want to make a separate worksheet which extracts these datas from any
of the monthly sheets for a determined date - making a Daily Production
Report.
I guess the right function will be the VLOOKUP - I have no problem with
using this function. But how can I define that it searches in the right
monthly sheet?
Secondly how can be this DailyProductionReport worksheet (only this) be sent
to pre-defined mail-recipients?
Any help would be much appreciated.
FicsiPapa Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770298
Partitioning Excel cell contents into separate cells
I have acquired a large spreadsheet with a column of cells each of whose
contents are like [1.234 6.712 5.436]
Numbers like these, contained in square brackets, are from another
application. How do I extract each number and place them in cells in
adjoining columns, to get:
column 1 column 2 column 3
1.234 6.712 5.436
? Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770297
New Macros Not Being Listed In Macro Dialog Box?
I inserted in a NEW workbook a Chip's Macro presented below;
Then I also saved the file;
Closed & reopened the same;
But upon Alt+F8 the macro name is not found to be listed in the Alt+F8
dialog Box nor can be run from VBA editor by Alt+F5?
Why???
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim DateStr As String
On Error GoTo EndMacro
If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Formula)
Case 4 ' e.g., 9298 = 2-Sep-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 1) & "/" & Right(.Formula, 2)
Case 5 ' e.g., 11298 = 12-Jan-1998 NOT 2-Nov-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 2) & "/" & Right(.Formula, 2)
Case 6 ' e.g., 090298 = 2-Sep-1998
DateStr = Left(.Formula, 2) & "/" & _
Mid(.Formula, 3, 2) & "/" & Right(.Formula, 2)
Case 7 ' e.g., 1231998 = 23-Jan-1998 NOT 3-Dec-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 2) & "/" & Right(.Formula, 4)
Case 8 ' e.g., 09021998 = 2-Sep-1998
DateStr = Left(.Formula, 2) & "/" & _
Mid(.Formula, 3, 2) & "/" & Right(.Formula, 4)
Case Else
Err.Raise 0
End Select
.Formula = DateValue(DateStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid date."
Application.EnableEvents = True
End Sub
--
Best Regards,
FARAZ A. QURESHI Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770291
Pivot Table Calculation
Hi there
I have a worksheet which contains a list of the products we analyse each
month and columns for the date the product was submitted for testing and the
date the product was released from testing. I also have a cycle time column
which is the number of days it took for the product to be released from the
date it was submitted.
I have created a pivot table from this data so I can look at individual (or
groups) products to see how many were released each month (count of release
month) and what the average cycle time for the testing was each month.
What I need now is to calculate the average cycle time per product per month
to use as a baseline however when I add a calculated field to the pivot table
and create the formula "average cycle time"/"count release month" I get
incorrect figures.
For example, "average cycle time" = 8 days "count release month" = 4
products which should give me average cycle time / product of 2 days - I get
something like 0.004.
Can someone tell me what I might be doing wrong or whether it's possible to
do this calculation in a pivot table?
Thank you, Silena Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770289
MATCH & INDEX ISSUE?
I have a table with agents in column A, with various rates for various
services in Columns B-F.
I want to look up agent X and service Y and have the cell return the
correct rate.
Pretty simple, but I'm stumped!
HELP! Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770283
Can you determine if hyperlink points to empty folder?
Our little cabana club uses Excel for our a photo id system. Each member
household has a folder that contains photos for their family members. One
column of the spreadsheet contains a hyperlink to each folder. i am trying
to do something that requires me to know which folders are empty, and which
folders contain at least one picture. Currently i am keeping track of this
manually (putting an X the column next to the hyperlink if the folder is not
empty.) This is time consuming and only as accurate as the last time i
update this column. Also, it is possible i might put an X where it doesn't
belong or omit one that does.
So i was wondering if there was a way to automate this. Is there any
function or macro, etc. that would automatically determine if the hyperlink
points to an empty folder or not?
--
Thanks for the help - Denise Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770278
Watermark Causing Print Problems
I added a watermark to an Excel 2007 doc.
Now, when I print, the last page doesn't show any text. It's there in print
preview, but when I print, I only get the header (where the watermark is),
and the footer.
This is only an issue with the LAST page. The rest print just fine.
I can remove the watermark and print the last page and the text shows up.
But I NEED the watermark on all pages.
Any suggestions would be greatly appreciated, I've been trying to fix this
for 7 hours! Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770272
CONCATENATE Question
I have two colums one with a first name and one with a last name I want to
CONCATENATE the first name and last name together, but I only want the first
initial of the first name and then the full last name to upload to a database
for users, is there any way in access for this to be accomplished. Thanks Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770271
time stamp question
Ok i figured out how to do a time stamp, but know i am trying to format the
rule that JE McGimpsey provides in his website. So that the date stamp does
not appear in next cell but in another cell. Below is a link with the example
of the rule.
http://www.mcgimpsey.com/excel/timestamp.html
Thanks Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770270
Sorting Data Advice please
Assuming I have 20 Columns or more with headers.
Column C has a date field, and I have define a name for it "Date" relevant
to each row.
I want to sort all the rows in date order.
Question 1
Dragging the mouse over a large number of colums an rows could be tedious.
What is the quickest way to sort the rows in date order automatically
without having to select the cell range everytime a new row of data are
entered? Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770269
Name Conflict
I finally resolved the problem I had opeing a file. In the "Name Conflict"
box, it says, "name cannot be the same as a built-in name". Then it gave me
the "old name" and a box to enter the "new name". The old name was
"Print_Titles", after trying every name under the sun, I simply omitted the
"_" (under score) and entered the new name as "printtitles". The file opened.
I have no idea where the name "Print Titles" came from and no idea what
"built-in name" means, but the file opened.
--
Roger Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770260
conditional formating
Hi there
I am created a file to keep track of marks. I want every other line to have
the back ground fill in. Sometimes students names will be added or deleted.
I don't want to have to change the format for each line when a student is
added or deleted. Is there a way to do this?
--
Thank-you!
Ruth Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770259
file format question
I've created a worksheet for our group to use. However, one of our members
has quattro pro. Is there any way to convert to a format that she can use
without losing my formulas and such? Thanks. Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770250
2D graph with label
I'm trying to produce the following graph
Program Name Long Leverage Gross
M1 23.06% 80.00%
M2 23.86% 96.67%
M3 25.27% 134.00%
M4 25.44% 118.30%
M5 27.87% 128.85%
M6 28.88% 132.00%
M7 32.33% 247.10%
M8 36.46% 152.60%
M9 42.50% 45.61%
M10 45.72% 160.25%
I want Long on the X axis, Leverage Gross on the Y axis and for each
point (X,Y) I want the Program Name tab next to it. Everything in a
scatter plot.
Anybody knows how to do that?
Thank you much. Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770249
delete items
How do you delete saved items from under recent Documents
when you save as it keeps on recent documents
how do you delete them
Thanks Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770245
How to open a secured Access db from Excel
Hello guys
I have been trying to find a solution for this problem in the last 3
days.
I have a secured Access database. This db contains a Macro that i will
like to run from Excel. I need the code to run the Macro from Excel.
Any ideas?
Thanks in advance Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770244
Inserting Hyperlinks
I need to insert hyperlinks into excel aiming at movies on my HDD. (Don't
ask, its a favour for someone) Which works fine, until I save and re-open the
excel file. It appears to be because excel shortens the path and therefore
breaks the link.
I have tried the Properties, Summary and Hyperlink base fix but find this
doesn't work for me. The 'Cannot find specified file' error persists.
I have also used =HYPERLINK function with no luck either.
How can I stop exel shortening the path?
Andrew Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770241
Variable range column summation and averaging
Excel 2003 SP3
WinXP Pro SP2
Header in Row 1
Data starts in Row 2
I'd like to sum [SUM($Fx:$Fy)] a range of cells, n, in a column and obtain
their averages [SUM($Fx:$Fy)/n].
My issue is that I'd like to vary the 'n' (cell range) by column so as to
compare the fitness of any particular 'n' to another relative to my data and
datapoints.
So, I've manually setup 'n=4' and 'n=20' and 'n=21'. For n=20, each time I
need to manually count the starting point for the summation because the
previous 20 cells are blank. Then I need to change each column's 'x and y'
[start and end rows] to reflect the new range.
How can I setup the formula for the averages so I can just reference a value
in, say, Row 1 or 2? Hence, I could have Row 2, Column F be '4' which would
then propagate the general formula to start displaying at $F6 for the
averages of SUM($F3:$F6)/4? The formula would need to know to start at Row 3
and continue adding through Row 6 and then divide by 4. (Note: Row '6' less
Row '3' = 3 then add 1 and I get the value of 4 for the range, if you
understand what I'm trying to say here.) I need the logic/formula for the
IF(...) and the establish how to do the ROW starting and ENDING points
relative to the quantity of datapoints to average.
Maybe I'm not too sure how to test what ROW I'm on.....
THANKS!!!
Tom Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770238
Help
I have a workbook with numerous worksheets in it. Each work sheet has
information down colum A. In the first Worksheet i want it to gather all the
information in colum A of each persons Sheet. For example. In the main
worksheet it will say Employee1!$A$1 in Row 2 Column B. What i am trying to
do is copy that formula, but I dont want to have to change the names. Is
there a function code that i can do so that it will automatically change the
names?
I dont know if this makes sense, but any help would be appreciated. Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770235
Reference every 13th cell from another tab.
Is there a formula that will reference a cell in a seperate tab for every
13th cell.
For example: In cell R7 it looks at cell =+Equity!E166. Cell S7 needs to be
13 rows below =+Equity!E166...
So, R7 equals =+Equity!E166
S7 equals =+Equity!E179
T7 equals =+Equity!E192
I need to repeat this for multiple colums. Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770232
Bi-weekly date formatting
To define a payroll bi-weekly payperiod. ie. 02/24/08 - 03/08/08
Want to enter one date in cell C4. say 03/14/08
I know that the formula =C4-19 will get the start date 02/24/08.
I know that the formula =C4-6 will get the ending date 03/08/08.
How do you format the two expressions together (with a dashed between) into
a single cell: 02/24/08 - 03/01/08 ?
Thanks Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770224
Changing Columns and Rows around to sort
Excel 2003 - SP2
I have a spreadsheet given to me by a co-worker.
Example:
A1 B1
Name John
Title HR
Department HR
This goes down about 1500 rows. 300 some people are listed. Is there an easy
way to have the Column become a row. Example:
A1 B1 C1
Name Title Department
John HR HR
Joe IT IT
If I could take this sheet and do that it would make life so much easier and
I could sort by it. Is there a good way of doing this? Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770216
How to sort date columns?
I have a spreadsheet with the following column headings:
Name
Occupation
Start Date
Termination Date
The issue that I'm having is that I'm trying to sort by the termination
date. Not all employees have a termination date so therefore their row is
blank. I need excel to be able to sort by termination date but I also need
it to place all people that have a termination date at the bottom of the
spreadsheet. I tried to sort by the termination date but it's doing the
opposite of what I would like. It places all that have a date at the top
followed by the blanks. Is there something that I can do to solve this issue?
Thanks for your help Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770213
how to automatically add row column headings to inserted table?
I have inserted tables into Word and/or Outlook. I would like to "turn on"
the Row and Column headers so that the table is referenced. Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770211
Using data from multiple workbooks to generate a report
How can I automate collecting information from 30+ workbooks that all have
the same format (i.e. Cell B25 in all workbooks has the same type of
information) and generating a report or another spreadsheet from the
collected information.
For example, all of my workbooks contain information about equipment at 30
different locations. I would like to query all of the workbooks and get a
list of what is contained in Excel Cell B25. Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770207
Format Painter
I have tried the way it says to extend my formating down the column but it is
not working. Here is what I am doing 1. click cell with formatting I want to
apply. 2. click format painter button. 3.click and drag mouse to select cells
i want to appy the formatting. 4. release mouse button.. It says the
formatting is applie to the data in the selected cells. No, it isn't. I go to
an empty cell in the worksheet and add data to be used and the formatting is
not there.
What am I doing wrong. These instructions are from the Microsoft Excel book. Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770199
Color-coded cell references
I've lost the color highlights that show which cells my total refenences when
I double click on the total, anyone know how to get them back? Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770198
importing file into Excel
I will be exporting files from Crystal Reports and importing them into Excel.
My question is: is there a way i can remove all the Blank rows and Blank
columns in Excel in one shot or do i have to delete each line or group of
lines? If there is a way to do it in one shot how would i go about doing
that? I was thinking of doing a macro but the only problem i see is how
would i set up the marco to see if they is any data in any of the rows or
columns?
Any assistance would be greatly appreciated!
--
Thank you, Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770189
Adding Row on a protected wkbk
I have several staff that use wook books (wkbk) to keep track of there
clients. The cells with the formulas are protected, because they keep
typing over them and then do not know how to put them back in.
They often add and delete rows, which I have allowed them to do while still
protecting the formula cells.
How do I get the formual to duplicate in a row that has been added with out
having the formual cell unprotected.
--
Lisa M. Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770188
VBA to split contents of cell
I have huge sheet with thousands of non-standard lines of text. Some of the
lines have the following line in a cell:
ProjID=MFG-Company-DA-33 ProjType=BBAA Uplift=0 CostType=Time Mgr=Smith
I would like to understand how to write the VBA that will skip the lines
that don't match this format and then Break out the items that do. This is
what I have so far.
some sort of loop statement here
If activecell.value (left(5))="projID" then
' this is where a need the help. I want to move everything after ProjID= to
Type= to the next column then move everything after Type= to Uplift= to a
column 2 columns away and so on for the rest of the line.
else activecell.offset (-1,0)
loop
End if
Thanks for the help. I would use text to columns, but only few of the
cells follow the format listed above. Thanks Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770182
Sumif with multiple columns in sum_range
Hello,
I have a set of data that has a label in column A the sales in column
B, C & D:
Column A Column B Column C
Joel 500 677
Joel 575 752
Joel 650 827
Claudia 725 902
Tarzan 800 968
I'd like to run SUMIF where the "Range" is column A, the "Criteria" is
Joel, and the "Sum_Range" is B:C but when I use SUMIF(A:A,"Joel",B:C)
I only get the sum of column B which is 1,725.
Can someone hook me up with a formula that can sum both column B & C
in this situation.
Thanks, in advance
Daniel Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770174
Excel Formula
I am trying to get results for cells with dates in them. Who do get the
following results:
a2 = begining date (3/4/8)
b2 = ending date (3/5/8)
c2 = difference (1)
I do not want (zero) 0 to show up if the cells are blank
I do not want a negative number to show if b2 is blank
I do want a zero to show if the b2-a2=0
here is the current formula I am using
=IF((N73-M73<0)," ",(SUM(N73-M73)))
If the M & N are blank I get a zero in O73. I want O73 to be blank
If N73 is blank, O73 is also bank. This is OK
If N73 & M73 have the same date I get Zero in O73. This OK
--
Lisa M. Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770173
Excel Hyperlink to Worksheet
I have embedded hyperlinks in various worksheets which I have then saved as
web pages. The web page links will take me to the workbooks but they ignore
the worksheet portion of the address. E.g.: this address
"MonthlyReport.xls#Summary!A1" will open "MonthlyReport.xls" but it will not
take me to the "Summary" worksheet. It seems to default to whatever
worksheet I was in the last time I saved the workbook. Has anyone had this
problem? Thanks! Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770166
problem with zip codes and filter
I'm trying to sort a spreadsheet by zip code and some zips show as
"06901" and others show as "6901". When I sort for one I don't get
the other. I tried to manually put a "0" before the the "6901" code
and excel will not let me. Which is weird because it will allow it to
be imported from another program like that. Is there some way I can
format it to get the "0" to show in the column? Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770162
Wrap text in merged cells VBA
I have merged cells that need to wrap text. I used the VBA code several of
you provided and it is working great for the most part. The problem I am
running into is when I have more than one set of merged cells on the same
row. I have applied text wrap to each of them. Let's say I type in several
lines of text in the first merged cell unit and it wraps the text perfectly
and auto fits the cell. However, if I type only a single line of text in the
next merged cell unit in the same row, the whole row autofits to that single
line and covers up my multiple lines of text that I just typed in the
previous merged cell unit. Any suggestions on what may be the problem? If I
need to try to explain further I will.
Much thanks! Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770156
vacation excluding holidays
HI
I am trying to count the total of leave taken without the holidays and
the weekends
i have used the NETWORKDAYS but i dont know what is the formula in
order to have a total
of vacation taken excluding weekends and holidays so this is the table
that i have
i am using network days but its not working
Total =NETWORKDAYS(B17,C17)
B= from
C=To
Holidays
B C
C52 New Year 1/1/2008
C53 St Maron's Day 9/2/2008
C54 Good Friday - -
C55 Labor Day 1/5/2008
C56 Atha's Day
C57 Fitr's Day - -
C58 MondayEaster - -
C59 Independence Day 22/11/2008
C60 Christmas 25/12/2008
PLEASE ADVISE
TIA Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770155
Date Formula help
I am trying to calculate the number of days in a time period excluding
Sundays,
(i.e., 2/29/08 minus 2/23/08 = 5 )
Can anyone help me?
Thanks Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770153
Link a cell to a word in a sentence
I am trying to create work instructions that can easily be modified. I want
to link a cell to a word in the middle of the sentence. I have a table with
the list of parts and tools, instead of using item 1, 2 ... in the sentence I
would like to be able to link a word in the middle of the senctence to the
table down below.
Thanks Brendan Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770151
Open Text Files in Excel, Changed Behavior
For years I have opened text files in Excel using the right-click
"Open With..." in Windows. The files opened without triggering the
Import Text Wizard and were imported in one column. As of yesterday
(without any apparent changes to Excel or Windows), this behavior
changed. Now when text files are opened using "Open With...", Excel
"figures out" where it thinks the columns breaks should be. (Again the
Import Text Wizard is not triggered opening files this way.)
I've searched for settings that determine how Excel opens text files
by default, but found none.
Any ideas on how to go back to right-click opening the "one-column"
text files?
Thank you,
Patrick Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770147
Help w/ calculating weekly Avg
Hello,
I am not sure what formula to use to calculate the weekly average, which is
the range of the week data (Monday to Sunday). Example: date range from 2/4
to 2/10/08 week is the sum of data from 2/4 to 2/10 divided by 5 (weekday).
Below is a sample of data and results I want to achieve.
Data:
Type1 Type2 WeekAvg
Date ProdA ProdB ProdA ProdB ProdA ProdB
2/4/08 30 10 20 10 61 37
2/5/08 10 15 10 15 61 37
2/6/08 50 15 25 10 61 37
2/7/08 20 10 20 20 61 37
2/8/08 10 10 30 20 61 37
2/9/08 10 15 40 15
2/10/08 20 10 10 10
Result column: WeekAvg for ProdA = 61
(30+10+50+20+10+10+20+20+10+25+20+30+40+10) / 5
Result column: WeekAvg for ProdB = 37
(10+15+15+10+10+15+10+10+15+10+20+20+15+10) / 5
NOTE: 2/9 and 2/10 are weekend so does not need to show value. Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770144
Formula Help for Duplicate Entries
I have a spreadsheet of 3000 contact names
I am trying to assign these in the most "fair" fashion to account
resps.
My thought was to create a colum called "account_rep"
I then put
account_rep_name1
account_rep_name2
account_rept_name3
In,
colum1,Row 1 account_rep_name1
colum1,row 2 account_rep_name2
colum2,row 3 account_rept_name3
colum1,row 4 account_rep_name1
colum1,row 5 account_rep_name2
colum2,row 6 account_rept_name3
the pattern continues respectively through the list to row 3000.
However, what has happened is that
there may be more than one account rep assigned to an account.
For example in the list I have.
account_rep_name1 Suzie Queen ABC Company
account_rep_name2 Suzie Queen ABC Company
account_rept_name3 Suzie Queen ABC Company
so, I want to assign unique rep to the account the first time his name
hits the account down the list.
What is the best way to do this?
Thanks,
Robert Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770143
Text overlapping cell border above it
When I print from Excel, the text more and more is printing over the cell
border above it, the further down it is. How do I prevent this from happening?
K Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770141
Transpose issue
I have a series of email addresses from an Outlook email. I want to use
Excel's sort feature to sort these email addreeses looking for duplicates.
I have attempted to copy , paste special, and transpose to insert this
semi-colon separated row into an Excel column. It does not work (inserts as a
row)
Ideas? Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770140
Annual leave without holidays
Hi
I am working as an H.r and i am looking for a formula concerning the
annual leave
I have already found a formula that gives the total of the days
allowed without the weekends
But i do need to calculate the days taken excluding the weekends and
the holidays
A: starting date
B: Total of Days allowed
C: New Year 31/12/2008
D: Ester 24/3/2008
Etc
Please advise Tag: Excel-How to get sheet taskbar and toolbar at same time on page Tag: 770137
When I minimize the ribbon I lose the top header info and full ribbon deletes
the sheet taskbar or toolbar. Can you help
--
Roger Estelle