Pivot Table Question #12
I've created a pivot table and it works well - counts the number of items in
each category. What it doesn't do is count the number of blank cells. It
gives me a blank category but, alas, it is blank. I want the blank category
line to tell me how many blank cells I have. I know that this is possible,
but just can't seem to make it work. Can someone help? Thanks!
To count the blanks you could use a space character, or the formula ="",
in the source data, instead of leaving the cells blank. The pivot table
will be able to count those.
Betty Csehi wrote:
> I...General question re Error Handling
This is just a general question for my own education. I've dabbled in a
fair amount of Access VBA in the past, but have just started learning about
Excel VBA. In Access, we would use Error Handling in almost every sub, but
In Excel, error Handling seems to be the exception rather than the rule. I
can't even find much about it in the Excel VBA help, and there are relatively
few references to it in this forum. What gives?
Terry, I use it all the time. My favorite approach is:
Public Const bDebug as boolean = true or false (de...Index & Matching Functions
I have created a workbook with three sheets. The first contains data
pertaining to a project type and a specific ID number that I have assigned
For Example: 1 Areospace Ground Equipment Complexes
3 Maintenance Facilities
I have another sheet that contains a database of all my firm's completed
projects in which I detail several fields (i.e. project number,
description, location, cost, client, and its Project Type ID#-taken from
the list aforemention...SMTP - ISP Question
please accept my apologies in advance if this slightly off-topic but I
though this would be a good place to ask...
I am looking for some recommendations for a UK ISP who will register a
domain (web hosting not terribly important) and will provide me with an SMTP
feed. I have the connection and the public IP addresses already so I do not
need a bundled service.
Many thanks to all in advance,
...a Solver question
Hi, I am trying to use Solver to solve a scheduling problem.
We get orders every Sunday, and then schedule the production for the
following week. The order is as follows:
Prod1 Prod2 Prod3
M 4 1
TR 2 1
The numbers indicate the amount of products we need produced BY this
And we have schedule tables for two plants like this:
Prod1 Prod2 Prod3
M x11 x12
T x21 x22
TR .... ... ...Mo2004
Recently ( a few months ago) I started using MS Money 2004. Previously I had
been managing finances on excel with formulas I had created. This was wroking
great but someone suggested to switch to financial software to make things
flow a bit easier. Since using Money 2004 I have had some struggles:
#1 - My budget varies from month to month (who's doesn't?). Whenever I make
changes to my budget it apears to retroactivly change all previous months.
How can I get it to just change any given month? Along the same lines, can I
make adjustments to prior months?
#2 - At the end of...Compatiibility question
Since I am going to college I qualify for the academic version of Publisher
2003. If I buy the academic version is this completely compatable with
Publisher 2003? I anticipate sharing files with friends who have Publisher
One other short question is when is a new version of Publisher coming out.
I was not aware of an Academic priced version of Publisher 2003. However,
there is only one version available, so both will be 100% compatible.
The next version of Publisher is due out October/November of 2006.
Microsoft Publisher MVP
http://www....simple checkbox question
I really dont know much about VBA and/or macros and have what probably
is an extremly basic question. I wasnt sure how to word it so when
trying to search it i didnt really get any answers.
all I want to do is add several checkboxes to a spreadsheet, if the
user checks the box, I want the cell underneath it to be equal to 1,
or any number or letter, it doesnt matter. i just used 1 in an IF
formula but can change it if needed.
what i am doing is a timesheet so that users can keep track of how
many hours they worked each day, and if that day is a holiday, i want
them to be able to use the ch...Why doesn't what I enter in the function bar appear in the cell?
I have a workbook that contains several copies of a master template
On one of these sheets when I enter text, it appears correctly in the
function bar, but appears as a string of &s in the cell. This has only just
occurred, and as far as I know used to work OK, i.e. it used to appear as
text in the cell.
What could cause this and what do I need to do to put it right?
You wrote that the text appears as apersands (&). Did you really mean
If yes, then try formatting that cell as General--or anything but Text.
Chris Mitchell wrote:
> I...counting rows wen 2 arguments are true
is there any way i can create a formula to count the amount of rows when
2 arguments are true?
i have two columns with text in them, say A1:A10 and B1:B10 id like to
find a way of counting the rows when both ranges have specific text in
ACCOUNT TYPE[/B] [B]FULLTIME/PARTIME
say i wanted to count the rows wen account type = ordinary and
fulltime/partime = Fulltime. is this ...Implement Help function
I have a MFC dlg-based application where I want to implement a help
For example, in Windows XP, going to the 'Properties' of 'My Computer' will
show you a window with a question button on the titlebar.
Is it possible to have this in dlg-based application??
"Wes" <email@example.com> wrote in message
> I have a MFC dlg-based application where I want to implement a help
> For example, in Windows XP, going to the 'Properties' of 'My Computer'...HTTP and HTTPS sessions question
Some secure sites have HTTPS session stay secure from login till end of
communication with site(log off).
Some sites are HTTPS only when log in, after login, they become HTTP, and
become HTTPS only when log off. (Yahoo mail for example, etc)
What are the chances that session can be intercepted and sidejacked and
traffic content recorded, especially as I know this danger really exists,
and its carried purposefully and intentionally, by recording DSL traffic.
I've created a simple spreadsheet.
Column A is a list of 15,034 used Work Order reference numbers (many values
Column C is a complete list (25,000 WO numbers).
I want to get a separate list of unused numbers, but can't get the EXACT
function to work. ( I believe it should place a TRUE value in column D when
the numbers match.)
=OR(EXACT(C1,$A$1:$A$15034)), which I replicated down column D.
It gives me nothing but FALSE values, even though about half the numbers
BTW, I do get a TRUE value if I use =EXACT(C21,A1), so it may be an array
I=92ve created a sub to delete and open tables. It looks like this:
Sub OpenCloseTable(strTableName As String)
DoCmd.Close acTable, strTableName
With the code, however, I have to close and open the same table. I=92d
like to do something like this:
Sub OpenCloseTable(strOpenTable As String, strCloseTable as String)
DoCmd.Close acTable, strCloseTable
DoCmd.OpenTable (strO...IF Statement Question
I have the following statement that does what I want
If the random number is between 3 and 11, a value is generated, my problem is if the number is either a 2 or a 12, I want to get another number
Random Number List 1 List 2
2 --- 8
7 0 n/a
12 --- 4
So if a number is between 3-11,...Offline Address book question.
Several articles about OAB referes to "siteFolderServer" attribute in AD.
However I'm bit confused as to which object in AD is being referred. What I
found that at least two Exchange objects has "siteFolderServer" attribute.
- CN=Default Offline AddressList,CN=Offline Address Lists,CN=Address Lists
- CN=First Administrative Group,CN=Administrative Groups,CN=<First
Can an...Licensing question
Could a 2 lane license be used for a company that has 2 separate stores with
separate databases that are on the same network? Could there be a 3rd
computer on that network that could switch from database to database to run
reports, order and receive for both of these inventories? Or would they need
to get HQ?
You could look at the Multi-Location add-in from Retail Realm
(http://www.retailrealm.us) . It may fit your needs, but the two locations
would be considered a single store.
What you are asking will not work though, two databases means 2 dongles.
The third management station...VLookup in Excel 2007
In Excel 2003, when you looked up in the Criteria table, the identifier you
chose had to be on the end and in alpha numeric order. Is this still the
case? If I was to vlook up in multible tables and generate my own unique
information, Can I go to a table created in the vlookup and use it even if it
is not in alpha numeric order?
Is there a work around?
I don't really understand your question, but the quick answer is that there
is no change in Vlookup from 2003 to 2007. You have always been able to use
a table that wasn't in order by using False as the fourth parameter....table
with a vba macro I populated a table, the size of this table is variable in
each execute this macro.
and with this table I create pivot table, Database functions, but
How to use all resources with all data?
suggestions and examples
Create a dynamic named range for your data.
Insert>Name>Define>Mydata Refers to =OFFSET($A$1,0,0,COUNTA(A:A),10)
The above example would create a range which contained as many rows as
are present in column A, and the would be 10 columns wide.
Change the formula as appropriate to your data location.
Then in the Pivot Tab...Trim Function
I am trying to use a macro to trim a selected range
but I can't seem to get it to function - the code
looks like this
Dim MyString, TrimString
MyString = " <-Trim-> "
TrimString = Trim(MyString)
TrimString = "<-Trim->"
I would be grateful for any suggetsions on how I can get
this to work
Thanks in anticipation
Dim rngTemp As Range
For Each rngTemp In Range("A2:R600")
rngTemp.Value = Trim(rngTemp.Value)
A...The hide PivotTable items functionality is gone in my excel 2000
The hide PivotTable items functionality is gone in my excel 2000, meaning I
do not see any drop down box to push... Any thoughts?
Your file is corrupted or MS will be sending you an ad for 2007?
> The hide PivotTable items functionality is gone in my excel 2000, meaning I
> do not see any drop down box to push... Any thoughts?
No worries... When excel had a day to cool down, it decided to show me the
drop down box'es the day after.
"Frank ...Charting/ Graph question
I'd like to visually display a table that contains the following table
StaffName Software 1 Skill Level Software 2 Skill Level (10
software titles >)
(around 50-60 people)
What i'd like to is output a simple visual output that shows me how
many people are at a particular level for the each software (but all
on one chart). I imagined that it may be nice to see blobs based on
the size of the number of staff at a particular skill level for the
However i am not sure if this is the right way to represent this data.
I've created a se...Word Art Question
I am trying to do a circle of 8 words. I want to alternate between blue and
green in each word. I have tried to do 2 semi circles of 4 words each but I
don't see how to alternate colors. I see how to make all words a blend of
colors or to do a pattern. Any ideas for me? I need them to be about 20pt
Copy the WordArt, paste special as a Windows Metafile (.wmf), ungroup, delete
the transparent background, select each letter and recolor. Regroup.
A circular example here, scroll way down
Mary Sauer MSFT MVP
http://office.m...How to see columns from which values were not returned by HLOOKUP functions?
How to see columns from which values were not returned (or were returned) by
HLOOKUP functions on a worksheet? All formulas contain only HLOOKUP
functions. The table_array s include all columns on the worksheet.
If I'm interpreting your question correctly, one solution would be to
run a Histogram of the formula results. That would show how many
occurrences of each result were obtained.
If you're unfamiliar with the Histogram function, you need to have
installed the Analysis Toolpak Add-in (go to Tools/Add-ins and click
on Analysis Toolpak), then go to Tools/Data Analysis and s...VLOOKUP Question 03-04-10
I'm using the VLOOKUP formula and I cannot determine why there is no return
on certain records, which should have a return.
For instance, I execute a macro to perform the VLOOKUP on one list, which
entries like: (TEXT)(SPACE)&(SPACE)(TEXT). The list I am cross referencing
has the exact same listing (ie I check =LEN and they match, I compared via an
IF statement and they match), yet I get #NA. The listing are text and the
range_lookup is not valued in the formula, so my thought is that the "&"
character may be the issue.
So, Are there any characters that...