Pivot table question: Sorting the subgroup in a pivot table (when using the top 10 option for the subgroup)
I have created a pivot table which has a one subgroup in the second
column from the left. It uses the Top 10 option, so there are 10
So my data looks something like this:
Group 1 50
Subgroup 1a 10
Subgroup 1b 40
Eight more subgroups
Group 2 30
Subgroup 2a 20
Subgroup 2b 10
Eight more subgroups
What I would like to do is sort by THe totals for Group 1, but then
also sort by the subgroups, based on the values for each subgroup
(similar to the "Sort by, ...Copying All Records from a Query to a Table
Here is another one!
I have a code which copies one record from a query(TODAY CHARGES) to another
table(RESPEL TEST ALL CHARGES).
I want the code to copy all the records in the current query to the table.
Here is the code I have:
Private Sub Toggle1_Click()
Dim PoseidonHotelProgram As Database
Dim rsta As DAO.Recordset
Dim frm As Form
Dim Msg, Style, Title, Response, MyString
Set db1 = CurrentDb()
Set Myset = db1.OpenRecordset("TODAY CHARGES")
Set Myset2 = db1.OpenRecordset("RESPEL TEST ALL CHARGE")
Myset2![Date] = Myset![Dat...pivot chart gridlines
I need to make major gridlines between the outer of my two X axis groupings
in a pivotchart. In other words, I have 2 levels of rows in my table, which
translate into an "upper" and "lower" axis label for the chart. I want to
have gridlines that show the divisions between the lower axis label (the
ones that occur less frequently).
Any way to do this? I am using '07. When I've tried to select the axis, it
is treated as one general one, not as two sets of grouping. So my problem is
how to isolate the one of the groupings and indicate I want to have gridli...Comparing data in tables
I have two Tables, A and B. Both have the same
structures, keys etc. Both are downloads from another
Table at different times. I would like to be able to
compare the data in these two tables to highlight the
different records. Can anyone advise how to do this,
I would use conditional formatting with a formula like
for the first and
in the other
and set a cell colour (pattern)
(remove nothere from the email address if mailing direct)
"Gerrym" <firstname.lastname@example.org....join multiple pivot tables?
How can I link up multiple pivottables all based on the same data where all i
would have to do is change the page option (drop down menu) from the first
pivot and the rest of the pivots would automatically adjust to match those?
There's a sample file here in which you can change the page field in one
pivot table, and all the related pivot tables change:
Under Pivot Tables, look for 'PT0021 - Change All Page Fields' or
'PT0008 - Change Multiple Page Fields'
> How can I link up mu...Loop through Table while Renaming Files
I need to loop through the a table to make the following changes:
If [Photo1] Like "Photo*" Then
fOK = RenameFileOrDir([Path1] & [Photo1], _
[Path1] & IIf([K_PID] Is Null Or [K_PID] = "" Or [K_PID] = " ", _
[Path1] & [TAXPINNO] & "_" & [Round] & "_P1.jpg", _
[Path1] & [K_PID] & "_" & [TAXPINNO] & "_" & [Round] & "_P1.jpg"))
...Help with Pivot Table subtotals?
My Pivot Table looks like
COOLANT 50/50 Total
COOLANT 60/40 Total
I'd lke to get rid of the "Coolant 50/50" and "Coolant 60/40" subtotal
lines. Double-clicking "Coolant 50/50" also makes "Gallons" go away. Is
there a setting or property that will do this?
Apologies - I tried pasting in an actual table from Word and it didn't take
But - I did find the answer: Field Settings, select None under Subtotals.
...Empty Dataset Tables Don't Save to XML
How do you get around the problem of empty tables in a dataset not being
included when you do a WriteXML? When you later read the data back into a
new dataset, those tables are gone, because they never got saved to the XML
file. Only tables containing one or more records get saved to the XML file.
I would get around this problem by programatically inserting a fake record
into the tables, then saving the xml, and then just don't save the fake
"Phil Galey" <email@example.com> wrote in message
>...How to permanently change automatic color assignments in a pivot .
I am displaying several measurments over time for on pivot chart for one test
sample at a time. I changed the line color so to convey results more
"intuitively" But found that once I select a different test sample to
display, all color assignemnt return to default. How do I change the default
line color assignment for series?
Found one trick is Tool < Option < Color, then click on each color block for
chart lines to modify to color sequence. Still unsure of how to change the
"default" marker shape and color
> I am displaying se...Pivot Table Counting
I am having trouble trying to calculate fields through a pivot table.
Basically, I have a data set that looks like this:
Record Number State Value
1 CA 0
2 CA 12
3 CA 15
4 NJ 0
5 NJ 10
I want to set up a pivot table that will show 2 columns, one with th
total number of records and one with the total number of records wit
non-zero values. Both of the...Pivot Tables #4
Hello you experts out there.
I nned some Pivot table advice ( a website address for
basics would be great), for instance how to excplain
pivots and how to create them.
I use lots of products with lots of sizes from various
suppliers to name but a few columns. Is there a limit as
to the amount of columns of data?
Thank you very much.
check out Debra Dalgleish's site - http://www.contextures.com/tiptech.html -
she has a link to Jon Peltier's site which explains PT basics and then lots
more interesting info on them.
check out www.hcts.net.au/tipsandtric...Pivot Tables
I was windering if some one could provide me with a link to a tutorial for
what I am trying to do.
I have an aggregate table. This table has several groups and several
columns. The intersection of a group and column is an aggreate value such as
Sum function of a column value in that group.
Normally a Pivot table shows 1 aggregate value at the intersection of a
group and a column. What I want is:
To show more than one value at the intersection besides the aggregate value.
In other words, if a simple pivot table cell is:
Aggr(x,y) where aggr is an aggregate function such a...formula in pivot excel 2007
It used to be possible to enter a formula within a pivot table so it would
update if the pivot table was changed. E.g. one column divided by another and
the formula would be avaiable even for extra lines if another variable was
used. Is this still avaiable in new excel, and if so, where?
In EXCEL 2007 take the following action:-
The Insert Calculated Field pane should launch.
In here you are able to set up to divide one column by another.
If my comments have helped please hit Yes.
Thanks....Filter Table based on Cell Value
I have an Excel (2007) worksheet that is linked to an MS Access (2003) table.
When I refresh the data, the entire table comes over. I would like to filter
this data based on the value of a cell in a different worksheet (same
workbook) so that it only brings in the data for the specified value. I would
also like to specify which fields I want to bring in (like I said, the whole
table comes over).
Example: My Access table has daily transaction records and a "transaction
date" field. I would like to only bring in records that have a "transaction
date" that is...Linked Table
Does anyone know which format I have to use in excel so when I link it to
access it will be converted to memo (Allow Zero Lenght = No)?
I have a workbook with several data columns and one of them contens big text
which I need to send to access on a daily basis.
What is happenning is when I link it to access I am losing party of the text.
Thank you very much.
The default registry setting for the "TypeGuessRows" key is 8. Therefore, if
your first (8) rows of data contain less than 255 characters, the data type
will be Text, thus t...Pivot Table #5
I got a block of data in Excel - sheet 1 (26 columns by
806 rows). I try to create a pivot table on sheet 2 BUT
whenever I try to add more objects to the layout I receive
the following error: "MS Excel cannot make this change
because there are too many row or column items drag at
least one row or column item off the pivot table, or to
the page position..."
What can I do to display all the data?
Please help, this is rather urgent!
From a post by Tom Ogilvy:
XL97: Limits of PivotTables in Microsoft Excel 97 (Q15748...Table question
I have WinXP. I created a Table in WordPerfect (6.1) and then opened it up
in Word (2003) so I could send it to some people who only have Word. In
both instances (WP and Word), there is a blank page at the end that I don't
know how to get rid of. Doing Delete on the last table page, or Backspace
on the blank page, doesn't work because the cursor is inside the table at
the end. Any suggestions would be appreciated. Thanks!
"Joy" <firstname.lastname@example.org> wrote in message
> I have WinXP. I...two pivot q's
I've been so lucky as to get new excel and am stuggling with two things I
can't do anymore.
1. Remove getpivotdata - if I click in cell B5 I want the formula to use B5
and not lock to a particular cell.
2. Base second, third, etc pivot tables on a first. There's a box for this
now, but I've no idea how to use it anymore.
I'd really appreciate it if anyone knows how to do these things.
Debra Dalgleish shows how to remove that annoying GetPivotData feature here:
Jon Pelti...Conditional Formatting
This is a multi-part message in MIME format.
I have [form1] linked to [table1] which has field [address]
I would like to conditional format field [name] (on [form1])
if [form1]![address] matches any [address] field in [table1]
I tried using Dlookup in the condition field but couldn't get it work.
Content-Transfer-Encoding...Pivot table from multiple sheets
I have data in multiple sheets. I am trying to create a 3D pivot chart. I
have the chart in 3D form with data from one column of the sheet and I need
to grab this same column (Column H) from multiple sheets and graph the data
in a Count of column, data, sheet name. If it sounds confusing, it's really
Y=Count of Version
...Pivot table labels
I have a pivot table with the months for label headings. Instead of
Jan-Feb- Mar, its Jan-Mar-Feb. How can I change the order?
You can drag the columns into the correct order, or --
Select the heading cells
Click the Options button
From the dropdown, select 'Jan,Feb,Mar'
Click OK, click OK
> I have a pivot table with the months for label headings. Instead of
> Jan-Feb- Mar, its Jan-Mar-Feb. How can I change the order?
Excel FAQ, Tips & Book List
...Excel Pivot Table
I have a pivot table where I created a calculated field that averages three
of the fields in the pivot table. My problem is that it averages the "blank"
(which are created from blank fields in the source data) fields instead of
ignoring them. How can I average the fields in a Pivot Table and not include
the blanks in the average?
Blank cells will add nothing to the Sum total, and Count will only count the
cells with numbers; hence you can obtain the average for the numbered cells.
"Jeff" <Jeff @discussions....Subform link to Master table
I have two tables. One is called tblMaster and contains information about
projects that are project specific such as Project ID, Manager, Vehicle Code,
Customer, etc. The other table is called tblProjects and has all the data
for each project. The only field that is different with each input is the
field called OpenIssues. What I am trying to do is create a sub form view
where the end user can select the project number and it will populate all the
data in the form from the tblMaster. Then on the form the end user can
update the project status.
I have created the form but have ...wrap text in a chart data table #2
Im have created a graph, which ive had no problem, but i when i click the
option to "show data table" i cannot get the titles to wrap to the box, i
have searched but i cannot find an option to wrap the text as i need it for a
I do not want to just simply alter the font size if possible. Is there
anyway of doing this?
...create pivot table from multiple sheet (excell 2003)
as understood, excell 2003 have row limit...but, currently i have data that
more than the row limit...
so, i split my data to 2 separate worksheet...the column name for each
my question, how can i create one pivot table from the two worksheet...i've
tried using the wizard n consolidated data but failed....
thank you vm..
Maybe you could try creating the pivot directly from the Access source table
(which doesn't have the 65k row limitations)
In Excel, in a new sheet,
Click Data > Import External data > Import data
Navigate to where the Access file is >...