Counting results of a formula
I have a column that has about 111 rows to it. In each row is a formula that
will find an invoice number if it exists elsewhere. The are alpha-numeric
I need to count the number of invoices found. If I use the COUNTA formula
for that column it will count those cells that have the fomula in it but no
result. I only want to count those cells that have a result from the
formula. Any ideas for using Excel 2003? Thanks.
Gord Dibben MS Excel MVP
On Tue, 4 May 2010 10:10:01 -0700, Nadine <Nadine@discussions.mi...Business Type Code Vs Industry
Can someone explain the difference between Business Type Code & Industry?
What is the role or purpose of Business Type code. If anyone knows of any
resources that could explain the intended purpose of this and some of the
other fields on the account object such as Classification, and Category that
would be great.
It totally depends upon your business requirments. If you think some field
is not required then just remove it or modify it to suit your requirments.
There are many attributes of Account which are not even shown on Account Form
by default but they are there in ...Code Comment Web Report,
I have enabled VC++ XML comments in Visual Studio 2005. However, I do not
see the "Build Comment Web Pages" under the Tools file menu (which my book
says should be there--but the book was written for VS02). Did they get rid
of Build Comment Web Pages in VS05, or simply move it?
> I have enabled VC++ XML comments in Visual Studio 2005. However, I do not
> see the "Build Comment Web Pages" under the Tools file menu (which my book
> says should be there--but the book was written for VS02). Did they get rid
> of Build Comment Web Pages in VS05, or simply ...output to
I created a button to save a report in a designated folder. The date
parameter is a box on the form the query for the report runs off of. I want
to include that date field in my report so I know what parameters I ran the
report on by looking at the contents of the folder. I tried to code the
start date into my stOuputFile portion but didn't do it correctly. Here's my
code so far for the button. Let me know if anything should be cleaned up as
Private Sub Cmd_MailEngineerRpt_Click()
On Error GoTo Err_Cmd_MailEngineerRpt_Click
Dim stDocName As String
I am trying to create a macro using VB. Here is what I want my macro to do :
I have file1 and file 2 saved in each monthly folder like
I have a spreadsheet like this
A B C D E
Date Unit No. Sale Amt Source1 Source2
9/4/2005 UN01 $2.00
6/8/2004 UN02 $3.00
10/5/2004 UN03 $5.00
My file1 like this
colum A colum B
Unit No Source1
Now I need go to file1 to find data for Colum D(Source1) by using vlookup
based on the unit number then extrive the data to colum D and times 50%.
So after I run the ma...Design for future merging
I've created a small but somewhat complex relational database that uses
autonumber fields in higher level tables as primary keys to link with foreign
keys in lower level tables. At this point I have up to 5 levels of tables.
I'd like to copy this database for use at 4 other remote sites, with the
ultimate intention of merging all 5 back together after a few months of data
entry. I've read quite a bit about how to merge databases that weren't
originally designed with future merges in mind, and this ends up being quite
complex with so many levels and autonumber-dependent t...Using AutoSum Button results in miscalculations
I have a workbook that has a couple hundred worksheets in it and one o
the worksheets is pulling values (via formulas) to it from many of th
other sheets. Now, all the values have apparently pulled ove
correctly, but when I run an AutoSum totalling all of the values i
adds them incorrectly. I verified this by using a calculator and b
retyping all the numbers in the next column in the worksheet and usin
AutoSum to total those numbers.
What could cause this? Is there a rounding problem somewhere? I hav
double and triple checked my formulas and they are all correct. I jus
don't understa...Numeric content in one cell ( implicit formula ) and the result in another one
I made some search before, but too much information at the same time.
So I post this question:
In cell D2 , I have the following content : 10002/(14971213 - 37375)
In cell E2, I would like to have the result value of data in cell D2
I would not work with "left...len... search... right.." as the format
( then formula ) in cell D2 may change.
Is there a function giving the computed result of a cell and put the
result in another one?
In cell E2, will the formula "=D2" do it for you? Or is that too
simplistic and I don'...Formula For Monthly Date
I have a worksheet that has products listed that are on sale at different
times of the year. My question is what formula to use to get all Jan
products,codes,prices etc on to new worksheet, Feb on another worksheet etc.
Use Autofilter. See:
> I have a worksheet that has products listed that are on sale at different
> times of the year. My question is what formula to use to get all Jan
> products,codes,prices etc on to new worksheet, Feb on another works...Date range in months from month and year fields
In our database we track consultants work dates in four seperate fields,
starting month, starting year, ending month, ending year.
Is there anyway to calculate the range of months they have worked with this
So if someone started March 2004 and ended July 2007 is there a formula to
calculate the 40 months they worked?
DateDiff("m",[starting month] & "/" & "01" & "/" & [starting year],[ending
month] & "/" & "01" & "/" & [ending year])
> In our da...Fixed Assets Import (6 months ago)
It really is a moot point. The situation is over, my clients are happy, and
I am able to import fixed assets just fine.
But, six months ago and right before service pack 1 for GP 9.0 was released,
for the first time ever I was unable to import fixed assets.
The FA Import Tool would show--on the "surface"--that all of my assets
imported. But, the fact of the matter was that the assets did NOT import.
And, there were no error messages givens to why!
I had opened up a case with Microsoft, at that time. They admitted that
they could not solve the mystery. And, they s...Does any have any sample code for Store Manager Addins
I'd like to jump start some development efforts and would like to see some
examples of how ADDINs have been used. Anything that hits the PurchaseOrder
Table would be perfect....
My understanding is that samples are availble to certified partners at the
PartnerSource web site:
It is not clear to me if customers have direct access to it or not.
> I'd like to jump start some development efforts and would like to see some
> examples of how ADDINs have been used...On error displayes default error code instead of the specifyed one
I have added error handling code to my combo box.
Private Sub searchName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
On Error GoTo ERR_Handler
Set rs = Me.Recordset.Clone
rs.FindFirst "[sdutentId] = " & Str(Nz(Me![searchName], 0))
On Error GoTo 0
MsgBox "Please empty search box before continuing!"
But it doesn't display the message in msgbox, it displayed default ms access
error code. which is "The text you have entered isn't an item in the ...Escape codes embedded in XML
I have built an XMLDocument object instance and I get the following string
when I examine the InnerXml property:
<?xml version=\"1.0\"?><ROOT><UserData UserID=\"2282\"><Tag1
QID=\"55111\"><Tag2 AID=\"5511101\"></Tag2></Tag1><Tag1 QID=\"55112\"><Tag2
I'm looking for an autopublish product for a customer of mine. I know of
one, but I'm trying to create a list of options for my customer, so if you
know of one, can you please reply to this post with details.
I've seen a couple folks develop macros to publish all projects. Not sure
if that's what you're looking for....
- Andrew Lavinsky
> Hi, I'm looking for an autopublish product for a customer of mine. I
> know of one, but I'm trying to create a list of options for m...code to change a form's allows?
I am wondering what the code syntax is to tweak a form's allows? Allows
deletions? Allows Adds? etc I know how to do it in the right-clicked form
properties manually, but I need it to be based on conditions.
Reason I need this is because I have one form that all users open.
Depending on their window's username, I have a security table that tells me
what role I want them to have in the database. Depending on their role, some
users have more rights than others on this form. I want to adjust the form
to allow additions for some users, but not others.
Me.AllowAdditions = Fals...Save Query Results as table to another Access Database
I have an access database that users use to pull reports (stored procs in sql
server) and it paste the data on 4 sheets in excel. Occasionally the reports
are too big for excel.
I'd like to develop a similar database that saves the results of the 4
stored procs as tables in a new Access database.
right now I have it returning the results as a query.
How do I save the results in another database?
Currently Using SQL Server 2000, Office 2000 and Office 2003
The following SQL statement should do it for you:
SELECT * INTO [TableName]
I...Modify Appointment Status Code attribute
Does anyone know how I can modify the pick list values that is for the
Status Code attribute in the Appointment Entity. This is the pick list that
appears when a user clicks on Actions->Close Appointment. All I can use is
Completed or Canceled. However, I would like to add more to the list. When I
open the attribute, everything is greyed out and I cannot modify this.
this field is locked because it is a system attribute, and i do not know of
any supported ways to customize/change values in this attribute.
"daryl&...Row highlight based on the 1-31 day of the month
I've got a table with dates displayed in the first column (ascending order).
I would like to highlight each row based on the day of the month with one of
5 chosen colours (red, yellow, green, blue and orange).
rows with the 1st of the month: red
rows with the 2nd of the month: yellow
rows with the 6th of the month: red
You would use conditional formatting to display different colors based
upon the day value.
Your conditional formatting formula would look at the day value of the
date. You will have multiple conditions for each row.
Formula is: ...Hide formula, show result
I have a formula typed into a cell. The cell displays
the formula not the result of the formula.
The options are set to hide formulas.
I know this has something to do with cell formats but I
don't recall the solution.
Please refresh my memory!!!
Could it be that you have:
tools|Options|View Tab|Formulas checked?
Or do you have that formula cell formatted as Text?
> I have a formula typed into a cell. The cell displays
> the formula not the result of the formula.
> The options are set to hide formulas.
> I know this has something to do with cel...Credit Card Approval Code
I am using IcVerify 3.1.6 to process credit card transactions and it is
working fine. But, the approval code printed on the receipt shows bunch of
numbers and letters (usually two lines long), instead of 6 digit approval
When I test the transaction using IcVerify program (not RMS software with
multi-user enviorment), the approval code shows only 6 digits.
Is there any way to correct this problem? Please advise.
Turn on the short answer file. In your ICV multi-user screen, type in a /O
(that's an O not a zero) on the initialization string line.
&quo...Rotate Label Code
Is it possible to get the details (with the rotated label control) to
display the results left to right rather than top to bottom?
I have the following now:
If Me.Left < (2 * 1440) Then
Me![Label0].Visible = True
Me![Label1].Visible = True
Me![Label2].Visible = True
Me![CheckNo].Visible = False
Me![CustNo].Visible = False
Me![PostingAmt].Visible = False
Me.NextRecord = False
Me![Label0].Visible = False
Me![Label1].Visible = False
Me![Label2].Visible = False
Me![CheckNo].Visible = True
Me![CustNo].Visible = True
Me![PostingAmt].Visible = True
End...How to query multiple zip codes
I have a database of over 380k records that I need to separate out by zip
codes. What I am doing is getting a 50 mile radius of my directors across
the country and this program comes up with a list of zip codes in that
radius. Just for OR, I have 7 pages of zip codes (182 zips). Is there an
easy way in Access to get only those zips? Some I can use the operator
between "97005-97024", but I can't do the entire list that way. I've always
wanted to figure this out. Thanks for any help to make this easier for me.
I have about 48 regions to do.
I do have ...If the result of a formula is negative make equal to zero
I do have a worksheet that calculates sales profits or losses quarterly in 3
different locations. For each quarter I do have a formula that gives me the
increase or decrease amounts during the last couple of years. For the total
result I need a formula that converts or ignores the negative results. Any
calculation result with a minus sign in the formula should be equal to zero,
so it's not included in the final quaterly total.
Could somebody help me with it? Thanks!
"Nrippe" <Nrippe@discussions.microsoft.com> wro...How to write a code in VBA?
I wanted to write a VBA macro to sort sheets in alphabet order (thx Chip
Pearson!), but I realized that I don't know how to do it in VBA.
I'm pathetic, I know, but can someone help, please.
I don't know where to write it, or how to make it work.
Ok, I get it now. I didn't realize I had to run a macro after I write it.
After I did that, the sheets sorted in alphabet order.
Told U - pathetic.
"Eternity" <firstname.lastname@example.org> wrote in message
> I wanted to write a VBA macro to sort sheets in alphabet order (thx Chip