Calculated field in a pivot table
I have a large pivot table in the format as shown at the bottom of
this post. I simply want to add a calculated field to the table for
each function that multiplies "Avg%Compt" by "Avg hours" for each MS#,
done for each Funct within each Subtype within each Type. ie MS1 for
Type A, Subtype 1. Funct ab, = 2.382 (60%*3.97) and MS2 = .5955
I cannot get this to happen. Can anyone show me how to set this
Type Subtype Funct Data MS1 MS2 MS3 MS4
A 1 ab Avg%Compt 60% 15% 15% 10%
A 1 ab NumPermits 301 301 301 301
A 1 a...two checking accounts data downloads to just one of the accts
I have two checking accounts with our bank and when I
download transactions into Microsoft Money for either
account, all the transactions go to only one of the
accounts set up in Money. I was prompted for the first
download to our "main" account. Then when I went to
download for the other account, I wasn't prompted where to
download to... and all the transactions went into the
first account. Did that make sense?
My bank required we switch to Money from Quicken, so I am
new to this. Tried to search in users guide and online
for help. Help would be much appreciated - ...summarizing worksheet data
I want to create what should be a relatively simple spreadsheet with, say,
fifty worksheets in the workbook. Each worksheet would be identical in terms
of number of rows & columns, as well as the type of data those columns
contain. On the first worksheet, however, I'd like to be able to summarize
the cumulative totals of all the other sheets. So, for instance, if cell a20
on worksheets 2 through 50 contained a formula that calculated a sum of the
figures in a1:a19, I'd like to put a formula in a cell in the first
worksheet that will total the cell a20 in all the other w...Gathering and adding data from different rows
I run a report at work every day that tells me how many shares of a
company have been sold and from what year these shares were granted to
the participant. The year and the amount of shares are on different
lines. For instance, the year would be on D5 and the share amount would
be on D10. This is a daily function and the data changes every day. I am
required to manually scan these reports and add up the shares for each
year separately. I have to separate the amounts for 2003, 1997 and
I want to create a spreadsheeet that I can dump this data into and have
the spreadsheet find...Adding a column in Excel 2007
Have a column with letters in it. Want to sum up how many of one
letter I have in the column. What is a formula to do this please
To count the number of cells that equal X
To count the number of cells that have at least one X in them:
> Have a column with letters in it. Want to sum up how many of one
> letter I have in the column. What is a formula to do this please
On Dec 10, 10:44=A0am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> To count the number of cells that eq...how can text be set up to generate a number?
i would like to a word generate a number, i.e.> enter "apples"in one cell and
excel generate "352" in a different cell for items like inventory.
You will need to set up a 'Lookup' table somewhere with
In two columns. Complete this for all entries and then if you are going to
enter 'Apples' on another sheet in A1 then in A2 enter
Microsoft MVP - Excel
"jwmaes" <email@example.com...how do I set-up a interveiw outline
how do I set-up a interveiw outline
"brazal" <firstname.lastname@example.org> wrote in message
: how do I set-up a interveiw outline
make a bulleted or
numbered outline via
DatabaseBen, Retired Professional
- Systems Analyst
- Database Developer
- Veteran of the Armed Forces
- Microsoft Partner
~~~~~~~~~~"share the nirvana" - dbZen
"brazal&...Pivot table and it's macro
I am creating a table and from the table a pivot table using a macro. When I
run the macro the table gets created without a problem but when it goes to
create the pivot table I get an error and the debugger highlights this
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
:="Perf Tables!R2C8", TableName:="PivotTable1", DefaultVersion:= _
Can anyone see where the problem is happening?
My ...Access unwanted automatic data entry
when entering data in my Access table, if i use the tab ket to advance to the
next field, it often enters a number in that field. Why does it do that nad
how can I make it stop!
Are entering data direcctly into Access table? If so, stop doing that,
unless you only do it occasionally to repair data in database and you know
database design and manage it.
"Pat the biologist" <Pat the email@example.com> wrote in
> when entering data in my Access table, if i use the tab ket to advance to...Macro to pull every Nth row of data
I have a spreadsheet of data and I need to pull every 60th row out onto
another sheet. Any simple macros?
Source_Sheet = "Sheet1"
Target_Sheet = "Sheet2"
n = 5000 ' your last line of data on Sheet1
Target_Row = 1
For nCount = 1 To n Step 60
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
F...Example using MFC WinInet classes with POST of FILE and other INPUT data
I am looking for a good example for POSTing both FILE and other INPUT
data to a URL. I understand that the FILE must use a multi-part encoding
but I do not understand how the atcual file contents get POSTed or how
the INPUT data gets POSTed also. I am guessing the INPUT data can be
added as part of the query parameters to the URL.
...Pivot Table % of
I have a pivot table like the followng:
Joe Trans Ct
Total Joe 10
Total Kelly 11
I added another Count of Trans shown as a "% of column", but it gives me the
percent of the entire column when what I really want is the % of Total Joe, %
of Total Kelly, etc. In this instance I simply made the names go across
instead of down which solved the problem, but there are instances where I
don't want to do that. Any suggestions for getting the percent per item
rather than pe...Last cell with data in a range
I enter weekly data into a spreadsheet with a summary page at the front.
After every week, when new data is entered, I want the formula at the front
to use the cell with the new data instead of me having to change the existing
For example, my data looks like this:
A B C
I need a formula that will automatically detect the last number >0 in column
Thank you for your assistance!
so your main page formula might be something like:
=...Data Validation in XL2007 suddenly stops functioning
I created a workbook in XL2003 that included some named ranges on Sheet2 that
fed some data validation lists on Sheet1. Everything worked as expected. My
colleague opened up the workbook in XL2007 and used it several times, and
everything worked as expected.
Then she called me, saying that it was broken. Of the 6 named ranges and
related data validation columns on the other sheet, none of them were working
anymore. I went over to her PC, and checked everything I could think of- I
could set up working data validation test cells on Sheet2, but could not get
the ones on Sheet1 to...Pivot table row header
In a pivot table, I have 2 fields in the row section.
How can I have the value on the first fields to repeat in every row instead
of just showing once?
Try Debra Dalgleish's page at:
Fill Blank Cells
Fill Blank Cells Programmatically*
'by Dave Peterson 2004-01-06]
GMT+8, 1� 22' N 103� 45' E
"Jason" <Jason@discussions.microsoft.com> wrote in message
news:08549CBC-68A7-442E-BABF-D48B11E29479@microsoft.co...Pivot Table Updates Issue
My Data had a coloum containing stuff like
I built a bunch of pivot tables on this data. Then I changed the data
and performed a Refresh Data.
Now, on the filter pulldowns on any of the Pivot tables, I see
The first three option no longer exist in any of the data, and I do not
want them in the list. How to fix this problem without recreating and
reformatting 38 seperate Pivot table...create pivot table formula without the GETPIVOTDATA function
Previous to Excel 2003 I could use a pivot table result in a formula and copy
that formula as a relative reference. In Excel 2003 a GETPIVOTDATA fromula
is created and uses an absolute reference. Is there a way in Excel 2003 to
either make it arelative reference (without editing the formula) or revert to
the method used in all prior releases?
There are instructions here for adding the Generate GetPivotData button
to a toolbar, and toggling the feature on and off:
> Previous to Excel 2003 I could use a pivot table resu...no option to add display name
I am running outlook xp on a both win98 and xp machines. In neither case
can I find or set a display name for my email account (ie so others will see
my display name). I have seen various suggestions about going to email
accounts/change but there is just no option there for 'display name'.
Is it something to do with the 'mode' of install?
any advice appreciated
David Marcus <dmarcus@netspeed.NO-SPAM.com.au> wrote:
> I am running outlook xp on a both win98 and xp machines. In neither
> case can I find or set a display name for my email account ...Excel macros page breaks but not on row 1 and column value in footer
Hi, I have seen some posts that are similar, but no one seems to have
the problem that I am having. I will post my macro in this, for anyone
that is interested.
My users get a csv file every month, and we have to clean it up. This
macro does that.
My last issues are this:
1) having the spreadsheet create page breaks whenever the value in
column B changes. Below is just that code.
col = 2
LastRw = ActiveSheet.UsedRange.Rows.Count
For X = 2 To LastRw
If Cells(X, col) <> Cells(X - 1, col) And Cells(X, col) <> Range("B1")
ActiveWindow.SelectedSheets.HPageBreaks...Stock Option Reporting
I am trying to run a Capital Gains report but my Stock Options are not
showing up on the report. Does anyone know a work around to report on Stock
In microsoft.public.money, shaunx4 wrote:
>I am trying to run a Capital Gains report but my Stock Options are not
>showing up on the report. Does anyone know a work around to report on Stock
If you are looking at Reports->CapitalGains report in Money 2006
Deluxe, why would your employee stock option be expected to show up
on that report?
Maybe I should have clarrified. I am trying to view stock options th...Conditional Autofill
Hi all! I've got this problem and it's really bugging me!!
Say, I've got Sheet 1 (attached) and there's some formula (assume it'
some VLOOKUP formula) on D3. I basically need some macro that woul
autofill the formula in D3 down to all the other cells in column D
EXCEPT for those cells with no corresponding value in the B column an
those with "Total".
Any help would be greatly appreciated. Thanks
|Filename: Sheet 1.JPG
|Download: http://www.excel...SetFont does not set font
Yet another problem I am afraid and I would once more like to call upon a
little external wisdom. I am having a major problem with CFont and the
SetFont. Mainly: It doesnt want to work.
I am using the CreatePointFont method on a CFont object defined in global
scope at the top of my dialog.
I then attempt to try and use SetFont on a dialog, and on a button, and on
anything else for that matter and the effect it has is zero. I am using it
in my InitDialog routine yet I still get nothing when I paint text to the
window, or when I view the buttons on the dialog.
Has anyone g...Appending data in cells that utilize a Dropdown list.
I would like to append entries in cells that utilize a dropdown list.
Currently, when I select (in this case names) from my dropdown list I cannot
append them if I want to add more than one to a cell. If I select another
name from the dropdown, it erases the first entry. Is there some way to
append entries within cells that use this form of validation so that I can
enter more than one in a cell from the dd list?
Office XP Professional
Debra Dalgleish has an example workbook showing how to do this
http://www.contextures.com/excelfiles.html...Importing vcalendar data to multiple users
I'm running Exchange 5.5. Several times a year I have to create a large
group of users. I'd like to be able to pre-populate each user's
calendar with pertinent calendar events.
Is there a way to import this type of data in batch mode?
See if http://www.slipstick.com/calendar/holidays.htm#company helps.
> I'm running Exchange 5.5. Several times a year I have to create a
> large group of users. I'd like to be able to pre-populate each user's
> calendar with pertinent calendar events.
> Is there a way to import this type of data in batch...Pass a command text in pivot table
I've got a pivot table with data give back by odbc (in excel 2003).
When i pass a new sql command, it answer me the database, also if i pass the
connection string. My code is this:
.CommandText = sSelect
.Connection = "ODBC;DSN=DS;DB=" & NomeDB & ";SRVR=;UID=SYSADM;PWD="
sNamePivot --> string variable contain the correct name of the pivot table.
NomeDB --> string variable contain the database.
sSelect --> variable contain the sql command.
Why does it called me ...