refresh pivot report
Every time I refresh my pivot table I lose formatting that I have to re-do
every time, specifically column widths, cell borders and cell formatting such
as bold and wrapped. I also lose this formatting when I use my page fields
drop down box to look at different groups of data.
Is there anything I can do to retain my formatting in these situations?
If you go into the Pivot table options you can de-select the box "Auto
This should do the trick.
> Every time I refresh my pivot table I lose formatting that I...Pivot tables remember values that are not available anymore
I would like to ask you is it is possible to reset the fields in a pivot
table to make disapear values that they are not anymore available on the
data form the selection-criteria when a fields is drill down
lets make a simple table like ::
uno dos tres
a q 1
s q 2
d q 3
f q 4
g q 5
a w 6
s w 7
d w 8
f w 9
g w 0
make a pivot table and drilldown "uno" (row fields), drillacross "dos"
(column fields) and "tres" as Data-Items
so in the selection criteria for "uno" ...Reference every 4th row from a master list of modifiable length
I'm creating an excel database for a clinical study on patients wit
migraine. Each patients will treat 4 attacks (episodes). Thi
presents a bit of a problem, since there's some information I'
collecting that's specific to the patient (demographic information lik
sex, height, etc.), and some that is specific to each attack (like th
pain level of that attack, the duration of that attack, etc.). T
account for this, I decided to give each patient four rows--one fo
each attack--and I put their deomographic information in the secon
row, leaving the other three blank in those fiel...How do I not print the column letters A-Z but keep the row #'s
I am trying to print a chart where I want to keep the row numbers but not the
column headings A-Z printed. Choosing Page setup under the Sheets tab only
lists them together not as single choices. Is there a way to customize the
sheet to only list the row numbers?
How about inserting a new A column and adding =ROW()
Bernard V Liengme
remove caps from email
"jgb" <firstname.lastname@example.org> wrote in message
>I am trying to print a chart where I want to keep the row ...Calculated items/fields in pivot table
I am new to excel pivot table and I would like to ask you all a
question regarding the calculated items/fields in pivot table.
I am making a pivot table whose data source is shown below:
Team Salesperson Amount Sold Price each
Team Person Amount Sold Price each
Paper A 50 35
Paper D 60 25
Pen D 500 4
Desk C 20 400
Desk A 10 700
Pen B 700 3.5
Clip B 500 2.5
Clip C 600 2
Desk B 15 600
I am thinking if it is a way to add a field in the pivot table
which can show the total sale of each Team. For e...A/P Tables
Which table(s) contain(s) the A/P checks/payments?
Which column hold the check number?
Are payments via credit-card in the same table(s) with checks?
You can find this information in the SDK found on the second Great Plains cd.
Payments are payments. The type is a column in the table.
> Which table(s) contain(s) the A/P checks/payments?
> Which column hold the check number?
> Are payments via credit-card in the same table(s) with checks?
> Many thanks,
The SDK indic...how to create a chart from a dynamic table of data?
i need to create a chart from a dynamic table,meaning that the table is
auto-generated from database after clicking a button.This is my first time
using macro in excel, so i'm not really well in it.
my idea right now is using Chart.Add,but then i'm having problem on how to
define the cells selection(i must do "the do..loop statement" rite?)
ur help is very appreciated,tq!
Use the macro recorder as a guide. When I record a macro to create a simple
chart it looks like:
' Macro recorded 7/30/2007 by Jon Peltier
ActiveChart.Ch...Hiding Rows Conditionally
Is it possible to hide a row only if a certain cell in the
row contains a reference to another cell that has a specific
Here's the scenario:
Column A of a Summary sheet contains a list of 20
categories. Each category row contains references to
data on another worksheet. Often, there is no data for
a specific category, so the Summary sheet displays the
category with zeroes or various errors depending on the
formulas referencing the data.
I would like to hide the entire row if the referenced data
cell contains a certain value, for instance "0". Can it be
done? Thanks for a...Combining rows of data that have one cell in common
Hi everyone, let me explain...
I'm working with a large excel file (13,000+ rows) of cancer cases recorded
in the state of Florida. Each time a patient presents with a cancer, that
"case" is recorded as a row on the spreadsheet, along with a UNIQUE patient
ID assigned to that particular individual. If that same person develops
another cancer sometime later, that information appears in a new case/row,
but with the SAME patient ID.
I need to export this file to another program (SPSS v13.0) to do some
advanced statistical analysis. However, the program requires that all of t...Pictures don't display in tables
Have been inserting jpegs into word tables for years. Suddenly the picture
doesn't display in normal or print mode. Instead I get an border. If I
click inside of the border the drag handles appear but they are not active.
It's a pain I can live with as the pictures do print however, I can't size
them. What have I messed up?
You might want to check if you have the option "Show picture placeholders"
Its help text indicates "Display an empty box in place of each graphic in
the document to improve scrolling performance....vb dataset row count incorrect
Has anyone else had this problem. Im using a vb .net dataset to read &
write XML but when I check the number of rows for a table it's incorrect.
Example number of rows = 4
table row count says there are 8 rows
does anyone know how to correct this problem ?
neil rowe wrote:
> Has anyone else had this problem. Im using a vb .net dataset to read &
> write XML but when I check the number of rows for a table it's incorrect.
> Example number of rows = 4
> table row count says there are 8 rows
That may have something to do with whitespa...Printing the first two rows as headers.
I would like to have the first two rows print at the top of every page
of my print out. I know this is a basic function, yet I can not seem to
find it in the help function. Could someone please post how to do
this /AND/ tell me what key words to put in the search/help function
that would show the answer as well.
J. Michael Morse
St. Ignatius <C:/Parish%20Administration/Graphics/Bulletin/Ignatius2C.jpg>
"Let no one ever come to you without leaving better and happier." -
Hi ...Unable to insert record in table
Using SQL Server 2005.
I have a table with one required field (key field, var type int) and various
other optional fields.
Yesterday, my Access VBA code failed to action the following SQL statement:
INSERT INTO ORDERS (OrderID) VALUES (9121165)
I got a time out error and a report that the record could not be inserted
(error code -2147217887).
So I opened SQL Server Managment studio and opened the table. When I tried to
insert a new record using the same OrderId, I first got a timeout error and
could not add the record. AFter OK'ing the error, I was able to cli...Looking up data from a table conditionally
I would like to know how to create a formula that looks at a value in one
cell, uses that value to find a corresponding value in a separate table, and
then enters that value in my sheet. For example, say I want a value to appear
in cell B1 based on the value in cell A1. If the value in cell A1 is "B", and
the value in a separate table that corresponds to "B" is $500, I want the
formula in cell B1 to return the value "$500". Any ideas on how to do this?
Classic application for the VLOOKUP formula.........
In my Pivot Table, I have all the column headers formatted to "wrap text".
When I select new data to display in the pivot table, it removes the wrapping
on the column headers. I've deselected the AutoFormat Table option, but the
Any way around this?
You may have to enable selection (From the Pivot toolbar, choose
PivotTable>Select, and click on Enable Selection)
Before formatting cells, instead of selecting the entire column, use the
selection feature to select the items in the description field. For
example, move the pointer to the top of a column ...Need Records from 2 Tables
I have an Employee table in Access that is updated weekly from a Payroll
system. If someone is terminated, they will no longer be in this table. It
was suggested I create a "History" table to keep from losing these terminated
records. BUT, this would mean when I query to find an associate, I would
need to look at all the records from the Employee Table, and just the
unmatched records in the "History" table. With what I know of joins, there
isn't a way to do this. So what do I do?
Any help would be greatly appreciated!!
If you move the terminated empl...Re: Preventing Appointments being made
New to this group and have read a little but not found what I am looking
Is it possible within Outlook 2000 to set up a series of day when someone is
on holiday so that no appointment can be added by mistake.
I know a message can be viewed at the top of the calendar when a series is
set as All day and Out of Office but this doesn't seem to prevent an
appointment being made by mistake.
Further to the above I would need to do the same with bank holidays. Also,
is there a way to make the bank holidays stand out in, say, another colour?
Any help appreciated.
Go into t...Pivot table from Multiple Consolidation Ranges
Dear All Excel Experts,
Creating a Pivot Table from 1 data table in 1 worksheet
gives me the Pivot Table the way I want it, meaning as
many Page Fields as I defined them at the top left of the
Pivot Table. These Page Fields represent the column lables
and in the drop-down I can choose the data under that
lable of the respective column.
Now I wish to have precise the same Pivot Table, but this
time from data tables spread over several worksheets,
whereby the format of these data tables are exactly the
same. But now the drop-down of the Page Fields at the top
left of the Pivot Table g...How to anchor data label show value option on Pivot Chart
I have a pivot chart and I like to show the data value above the column, however when I resort the chart with a new query, the data labels disappear and I have to go back through format data series/data labels/show value all over again. How do I get the data labels to stay and update automatically based on the new sort
This is an admitted deficiency in pivot tables and charts. Next time you
have to redo a chart's formatting, record a macra, then play it back
every time after that.
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Sol...How to Show/Hide Columns or Rows with no data via VB
I am need of some advice please.
I have a spreedsheet (2007) which I designed as an attendence register.
Currently it works well but I want to improve on a few matters.
For the main page I have a column with the date, next column day, then the
next 40 columns are set for the crew employees. These are broken into 4
groups of 10 for each crew (A,B,C,D, Shift workers), however there is
normally only 7 people per crew, the other 3 are blank unless there are extra
enployees to that crew.
Currently I am hiding and showing manually, but I want to be able to do
something via ...Linked Table
Hi, I have a Access 2003 mdb that I have used for years that has worked
OK. Recently one of my queries malfunctioned to an external Progress
db's linked table. Specifically, the query's table list is missing many
of the fields in the table though when I inspect the linked table in the
Tables Objects Section I see all the fields. How can I fix my query?
Why post the same question in different newsgroups? (already answered in the
other newsgroups you posted in).
If your situation is one of the (few) unusual circumstances in which it is
necessary/appropriat...Table Definitions #2
Where can I find the table layout and definitions of the object items in each
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=7e5ff417-b4b2-49b3-8a3b-e43117c11a2c&am...QTY Shipped in Purchasing Line Receipts Table
Does anyone know why the quantity fields in this table have a
"currency" storage type in table POP10500? The field type in the SQL
table is numeric. I want to do a Smart List showing receipt
transactions and the data comes out in currency format in my list.
Can this be changed in the database or is there some obscure reason why
it's coded this way? If so, where would it be changed?
NOVA Solutions LLC
It is a currency field for 2 reasons:
1. It provides QTY's greater than 2.1 billion (limit for long integer).
Probably not a real good reason as I&...Select Statement in query to append to a table
I have a table with the following
Master Stock Code
and I want to append this information to a table, kind of ike a cross tab
query but I want the value of the header ID to be in the column not as a
column name. I have a max of 12 header id's, so I have 12 column names in
the table i want to append to (ie. Hdr1, Hdr2 etc)
There could be more than one Header ID per stock code.
WHat is the proper syntax for the select statement in my query so I don't
get duplicate rows per stock code.
...Conditional Formatting Whole Row
Using Windows and Excel XP.
I have a worksheet that has the dates of this month in A1:A31 and other data
in B1:F31. I have set up a conditonal format: cell value
equal to =TODAY() to have the current date (December 6) in
A1:A31 to highlight in yellow. What I would like to do is not only
highlight the current date but also all the data in that particular row.
For example, in A1:A31, December 6, 2003 (cell A6) is highlighted in yellow.
How can I get the entire row (A6:F6) to be highlighted in yellow. Is it
possible to do that?
Thanks in advance,