Calculations using empty cells
I have a spreadsheet with three columns: Current balance, withdrawals, and
deposits. Current balance is determined by subtracting withdrawals and
adding deposits to the balance in the line above.
Suddenly I have started getting a cell entry
in the current balance cell.
A typical entry in the cell is for G43 would be:
=G42-E43+F43 and the sudden new result is #VALUE.
All three columns have cells formatted as numbers.
On Thu, 6 Mar 2008 16:22:02 -0600, Hank in KC wrote:
> I have a spreadsheet with three columns: Current balance, withdrawals, and
> deposit...Sorting a column by using formula #3
I am trying to use sort function just to delete blank cells in between
Sort order doesn't matter actually.
Data is coming by the use of simple cell reference of "another sheet
Praise's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1558
View this thread: http://www.excelforum.com/showthread.php?threadid=27144
you may use the following addin to filter out blank cells:
&g...Using Function to call Woorbook Sheet
I would like to use WEEKDAY() to call a particular Sheet in a Wookbook. The
Sheets are called Sunday - Saturday and I have an IF statement that needs to
check for particular information on that sheet depending on what day it is.
Have it currently functioning but it is taking up way to much space. I am
unable to update my code with other functions that I need it to calculate
until I can figure this part out.
=IF(OR(AND((TEXT(TODAY(),"dddd")="Monday"),(HLOOKUP(MOD(NOW(),1),Monday!$B$1:$BA$40,2))>0),AND((TEXT(TODAY(),"dddd")="Tuesday"),(HLOOKU...Run macro only if
Hi, i need a macro to run if A1 contains "John" and C1 contains "Mari", and
if D1 is blank. If this criteria is not mached, then the macro to display a
Can this be done?
Is this what you mean
Set sht = Sheets("Sheet1") ' change to suit
If UCase(.Range("A1")) <> "JOHN" _
Or UCase(.Range("C1")) <> "MARI" _
Or .Range("D1") <> "" Then
MsgBox "Criteria not met"
End Su...Prevent User to grant mailbox access permission
By default, in Outlook user can grant other user access
permission to his/her mailbox folder. But, our company
doesn't want this. Is there anyway to prevent user to
grant access permission to his/her own mailbox and sub-
Any input is much appreciated.
...use a time value in a bar graph
i have values in a series
I would like to use e5 in a bar graph it gives values which would make sense
only to excel
If the axis is formatted as time, it shouldn't be a problem. If it doesn't
choose the scales to give clean divisions, you can choose an appropriate
unit on the axis, such as 03:00.
"pdfrone" <firstname.lastname@example.org> wrote in message
>i have values in a series
> c5 9:01
> d5 21:01
> e5 =(d5-c5...Do I need DSClient to run Exchange 5.5 on an NT4.0 box in Windows 2003 Native Mode ADS?
I am in the process of migrating my WinNT4.0 domain and Exchange 5.5
Org to Windows 2003 ADS/Exchange 2003. I know best practice is to
change the domain to Native mode, but how does this affect my NT4.0
server running 5.5? Do I need to simply load the DSClient onto the
I should also note that we did an inplace upgrade from NT 4.0. This is
a single domain environment.
As long as you no longer have NT 4.0 BDCs you should be able to move to
native mode. Your NT 4.0 server running E55 will not be affected (unless of
course it is also a BDC).
It is a good idea to...Changing my functions to use subtotals?
I have 2 functions that are calculating my data correctly, except now I need
to consider the autofilter. I figure using subtotal is the way to go if I
want to disregard filtered out rows. Also, I plan on moving my data to
another sheet, so it will need to reference the current sheet, which is
AAT_Raw_Data. Finally, the # of rows of data will vary, but I don't think it
will ever be over 10,000. I'm using Excel 2003, so I think there's an issue
with using something like "A:A" in my formulas.
Can someone help me change these to add in a subtotal and take into...How to Populate .msg File Used in Command Line
I have a .msg file I saved as a boilerplate for Calendar requests that will
be programmatically started in an application I am developing. I want to be
able to modify the Calendar request's date, time, duration, and message
contents based on the user's inputs prior to opening the .msg file. After
the user verifies the contents, he/she will click the <Send> button.
Currently, from the command line, I can run
"c:\program files\microsoft office\office12\outlook.exe" /f
The Calendar request opens with the boilerplate. Ho...Con't use capplication object
I have developed an application.This application has a excel file list
control.When i double click in the excel file list control,the excel
application will run and open the corresponding excel file, and another
window which has another list control is opened. When I selected a cell in
the excel window and then double click in the list control,the selected list
control item value will be put into the selected cell.I use the following
m_ExcelApplication is the excel application instance...cleare space using shrink
I have server which has not enough disk space.
I have removed an unessesery data from my databases which is approx 50% of
the datbase which is 20 GB
As i know i need at least 20 GB on the HDisk to run the shrink (which i
don't have, at least 10 GB). how can i free space in this case?
...Problem using Microsoft Web Browser control
I am trying to access the Custom properties of a Web Browser ActiveX control
on a form and I keep getting a message telling me that "The Operation on the
Microsoft Web Browser object failed. The OLE server may not be registered.
To register the OLE server, reinstall it."
I searched TechNet and MSDN and could not resolve this. I reinstalled Access
2003 (I have 2003 and 2007 installed on my workstation, XP Pro) and still no
Has anyone else seen this and if so, can you tell me how to resolve this?
Does anyone know the name of the file the message...How to check if mouse wheel has been used, system-wide?
I have the following task - I need to check time to time if mouse
wheel has been used by the user, in ANY application. I have some
kind of user activity detector, to do certain actions in application
when user is idle for some time. It would be nice to have the
ability to check if mouse wheel has been used. I already know how
to check for keyboard (GetKeyboardState and compare with previous
call's result) and mouse (GetCursorPos and compare with previous
I know how to set up a hook, and can achieve what I need via hook.
But, customer says sometimes system runs slo...Create Assembly Call
can the create assembly call under update entity be used to add a certain
number of hours/days/months/years to a date.
i am trying to do this workflow
if variable 1 = yes
create a task
update variable 1 sent = yes
variable 1 date = execution time
can i use the create assembly workflow to add x number of days to variable 1
date and show in variable 2 date.
any advice would be helpful please.
If Variable 1 Date is a DateTime field in CRM, then yes. You can use the Add
DateTime assembly to take the Value in the DateTime field (which would be t...Using skins in a multiline edit box
I have a dialog based application, in which I have
a multi-line edit control.
In the edit control, I would like to have a background
image, say some .bmp file. Some status messages are to
be displayed in the edit control, based on certain user
actions in the dialog. How do I go about this?
you can do it, i think, if you were to create a class which inherited from
the edit control, then in the override the OnDraw()/OnPaint() event, where
you first get the rect, and draw your bitmap resource, then call the parent
event method to do the rest...
I don't know if t...One line is killing my program!
The following line works, where it specifies the first 4 characters of two
area codes (403, and (587.
.Range("C2:C" & q).FormulaR1C1 = _
I need to substitute two variables - areaCode1 and areaCode2 to replace (403
and (587. I think I've done so, but the following doesn't work:
.Range("C2:C" & q).FormulaR1C1 ...how to use a saved master page
I have created a new master page and saved it as a .pub file. The question
is when opening a new document, how do I apply the saved master file?
Open the master file, copy, paste to your new publication.
Mary Sauer MSFT MVP
"Colin" <Colin @discussions.microsoft.com> wrote in message
>I have created a new master page and saved it as a .pub file. The question
> is when opening a new document, how do I apply the saved mas...Setup Outlook 2003 to reply using Quotes
I have read some posts already but I am not clear on the
I have set outlook 2003 as follows:
Tools/options/mail - preferences tab - mail options, and then set to
prefix with a > for replys and forwards.
I get a whole bunch of > on some messages and mostly blue lines on the
side for the rest, so I can't do the proper quoting.
How is this supposed to be setup? Each time you reply should I cut and
paste the text I don't want in the message or is there something that
helps do that? If you can reply to this message and use the quoting
method as the other g...run ShellExecute but from a console application
I Need to run ShellExecute but from a console application. Problem is that
it expects to a window handle in the first parameter (other function like
"system" is not appropriate).
Can someone advise please?
Thanks in advance
NULL handle is allowed.
"Sam" <email@example.com> wrote in message
>I Need to run ShellExecute but from a console application. Problem is that
> it expects to a window handle in the first parameter (other function like
> "system" is not appropriate).
> Can someone adv...Running Office 2004 with network homes on 10.4
Im really at the end of my tether with office now, for well over a
year it seems as though no matter what I do office just behaves like a
Were talking 10-20 minute load times just to load an office app.
It will sit there and first it say configuring microsoft office
then through a host of other things until eventually as i said about
20 minutes later it will actually open.
I've seen things about fonts before, but i can tell its not just (if
at all) this.
I have a reasonably fast network, so i cant see how it is network
All clients have networ...Using Code instead of Criteria
In my query builder, I'm getting the message:
"The string returned by the builder is too long. The result will be
I think I understand why, as I am using a form to specify a large
number of options for the report.
Can I use VB to specify these criteria instead of the query builder?
Examples in query builder:
If [Forms]![boxSeatsMailings]![PW] Criteria =1 and (PFDirect>0 or
PFBulk>0 or PIDirect>0) then select record.
If [Forms]![boxSeatsMailings]![PW] Criteria =2 and (PFDirect>0 or
PFBulk>0 or PIDirect>0) then select record.
As ind...Can I use 'or' in conditional formatting?
I'd like to have 4 conditions, Red, Yellow, and Green. But I have two
conditions for green, without using two conditions for green is there
a way to use OR somehow to beat the limit?
PS Win2000 + XL2002
Sure ... just click on "Formula Is", and you can use "OR" for more then two
Will trigger the set format if "any" of the above equate to "True".
Please keep all corr...Using XSD stored in assembly resource with includes
I am trying to validate an XML file against an XSD that is stored in the
assembly as an embedded resource. I can get it to work as long as the XSD
does not include other XSDs. After a fair amount of searching, I have found
3 possible solutions but none have worked for me. They are:
1. Use the Includes property of XmlSchema to add included XSDs, then call
XmlSchema mainSchema = XmlSchema.Read( stream1, null );
XmlSchema includeSchema1 = XmlSchema.Read( stream2, null );
XmlSchema includeSchema2 = XmlSchema.Read( stream3, null );
mainSchema.Includes.Add( includeSchema1 ...How to clean 0x0E value from file when using XSL?
I have some code that cleans up an xml file before running xsl
transformation on it due to what I call bad characters in the data. I
have as an example:
str = Replace(str, "", ".")
str = Replace(str, "", ".")
In there as filters to strip out characters XSL doesn't like and
replace it with periods. I am now getting an exception of a hex value
of 0x0E however can't figure out what the string replace value should
be for this one. From what I can tell, it's some kind of "shift out"
character whatever that is. A...Try again: Using mail-to hyperlink with an attachment
I'm creating forms using Excel and Word. Somewhere in the form is an email
address. I can use Edit-hyperlink to specify the recipient address of the
email address as well as the subject field. Great.
What I'd really like is to be able to specify that the form file itself (the
..doc or .xls) is also attached to the email message. I'd like to specify
that right in the hyperlink.
Then it can act as a workflow.
I don't know anything about VBA so I'd rather not go there.
Is this possible?
No, I don't think it is possible without some amount of VBA - I c...