Is it possible in some way to fix the structure of a pivot table in excel or
even in Access so that I have a familiar layout even though the data may
vary from differences in the data. By implication, thefore to show empty
colums/rows somehow. This is very important to my situation.
I couldn't think of any way of changing the format of the
However to solve your problem, you could have your own
format in a different sheet (within the same file) and
link the pivet table using VLOOKUP( formula to get the
numbers to the right place.
You could use refresh data to upda...Query as Pop Up, is it possible?
Is there an option, or how can I run a Query as Pop up mode?
What I want is that the user can see the results in Crosstab query but I
want they do not have access to see the query in design mode neither make
I would like a pop up query but not a maximized window and the only option
that the user can see is the Close Botton, and another button to print the
query, is it possible?
Note: using sub-form does not work since in the crosstab appear some main
field that when the sub-form was built they were not there, and this cause
the sub-form show an error because th...pivottables
I created a pivot from a dataset. This was no problem at all. In a
later stadium I changed the recordset where the pivottable is based on.
For example i replaced all the records containing pete into mike in the
column <NAME>. When refreshing the pivot it now contains both mike and
pete. Pete is redundant, because i no longer exists. How do I get rid
Just drag the field out of the Pivot Table, then do a Refresh, then drag
it back again
"lennert" <firstname.lastname@example.org> wrote in message
news:1140180768.525075.3081...Does anyone know the PivotTable Refresh Event? #2
Thank you Debra it worked fine for me
JohnD'Elia's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1531
View this thread: http://www.excelforum.com/showthread.php?threadid=26930
You're welcome. Thanks for letting me know that it worked.
> Thank you Debra it worked fine for me.
Excel FAQ, Tips & Book List
I have a query that is derived from 3 tables with relationships. i want the
user to open a form and be able to change data in that query. i'm assuming
it is not letting me because it is derived from more than one table. is the
only solution to this is to create a table off the query and then base my
form on that table.
wasnt sure if there was a different way around this.
> I have a query that is derived from 3 tables with relationships. i
> want the user to open a form and be able to change data in that
> query. i'm assuming it is not letting me ...How do I add new data to a pivottable/chart?
I have a spreadsheet that I add new data to weekly. Is there a way to have
the pivottables/charts refresh to include this data without recreating them?
If I try to do extend the range of cells used to create them originally, I am
unable to group dates (which I need to do) on the chart because of blank
You can use a dynamic range as the pivot table source, and it will
automatically expand as rows or columns are added. There are
When you refresh the pivot table, all the new data will be included.
Brian Lee wr...Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook?
Wow! I tried this out, and it seems to work beautifully. It's odd, though,
that I haven't seen this technique mentioned in any of the several Excel
references that I've looked at.
Basically, I have a workbook with several worksheets...one worksheet
contains a large list with all the records.
I wanted to set up the other worksheets to have certain functions...I wanted
them to use only certain columns from the main list, and to contain only
certain records from the main list that met specified conditions.
This is the best solution I have found so far - i.e., creating database
q...Any Way to invite Outlook Calendar Sub-folders through the GAL?
We have sub-folder calendars set up within existing shared mailboxes. Is
there any way to get a link to those calendars directly, so that they may be
invited to a meeting (as a resource)?
Sue Mosher, Outlook MVP
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
"Jay" <Jay@discussions.microsoft.com> wrote in message
> We have sub-folder calendars set up within existing shared mailboxes. Is
> t...Query Optimisation problem. CTE with Ranking function used by12 x union stm
I hope you can help me with this one and get this query to run a bit faster.
I have a CTE which looks at data in the table and gives it a ranking number
using the rank() over (partition by ?? order by??) syntax. This is done
because I then need to run some DQ tests on the data, based on the ranking
order of the records.
(UK NHS RTT status codes for anyone who is interested...)
CTE statement is below.
with CTE as
rank() over (partition by R2T.Refrl_refno order by R2T.RTTST_Date,R2T.
SORCE_Refno) as 'Rank',
I'm interested in learning how to customize web Queries. Is the onl
way to do this is to use XML? I tried using the Data-> Import Externa
Data-> Web Query tool but I'm stuck with the layout thats on the we
If anybody has any links to any additional literature that would b
great as well. I looked in my excel bible but it only shows how t
pull the data & not customize it.
Phatchef24's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1093
View thi...dynamic sub menus without command ids
I am trying to create dynamic submenus without predefined command ids.
Having overridden OnMenuSelect, I dynamically generate the menu items (when
the correct menu to be opened), replacing an original dummy entry with the
int c = pPopupMenu->GetSubMenu(MenuDARSubSub)->GetMenuItemCount();
for(int x = 0; x < c; x++)
for(int m=0; m<=m_pDoc->m_namenD.GetUpperBound(); m++)
pPopupMenu->GetSubMenu(MenuDARSubSub)->AppendMenu( MF_STRING, ID_DAR0,
How do I use more than one Summary function for a PivotTable data field?
In the Pivot Wizard, add another copy of the field to the data area
Double-click on the new field, and under 'Summarize by', choose one of
> How do I use more than one Summary function for a PivotTable data field?
Excel FAQ, Tips & Book List
...Trying to Siplify a Query in MS Access 2003
I am a real Novice to MS Access 2003. I am trying to create a query to
populate a form to be used as a subform in another form. I have created the
form that use a table view of the a table. But would like to be able to use
text boxes to view the data. I do not know if I should change the table
designs that I am currently using. Here is what I have.
1. t_Contacts Table:
ContactID (Text value)
FirstName (Text value)
LastName (Text value)
Birthdate (Date/Time value)
ContactTabID ContactID FirstName LastName Birthdate
001 DonDuck ...Query General
I have two tables
Name Serial Date
Name Serial Date
I want is to assign the serial against each name starting from 1 in table2
according to date in ascending order and the max serial should be assigned
against each name in Table1 i.e..
Name Serial Date
John 3 01-01-2010
Tony 2 11-02-2010
Name Serial Date
John 1 5-4-2009
John 2 6-8-2009
John 3 1-1-2010
Tony 1 11-2-2010
To...PivotTable Column Field Values
The column field list of my PivotTable (when one clicks on the
drop-down arrow) is populated with a list of items which no longer
exist in the data table from the data is drawn.
Is it possible to reset these values so that only valid data can be
Please let me know if I have not explained the problem adequqtely.
There are instructions here for clearing old items from a PivotTable
Michael Rekas wrote:
> The column field list of my PivotTable (when one clicks on the
> drop-down arrow) ...Changing RecordSource Causes A Reload of Sub Form Destroying References
Apologies for posting in two newsgroups but after I originally posted it in forms I realized this
should have been posted here (formscoding) instead.
This only happens on some machines. It has occurred on a PC running Windows XP SP3 and a MAC
running Windows. I have been unable to reproduce it on other PCs running Windows XP SP3, Vista or
Windows 7. On the machines that have the problem it always occurs.
I have a form with a sub form control. The control's SourceObject is changed depending on the
function required. On the forms that is used in the control is a...pivottable
Is there anybody how knows what the shortcut is to deselect fields in a
pivottable? (I have a pivottable with 1000 possiblities for a field, but I
only need some of them)
Excel 2002 has a Show All checkbox to show or hide items in a field. For
earlier versions, you can use code. There are examples here:
> Is there anybody how knows what the shortcut is to deselect fields in a
> pivottable? (I have a pivottable with 1000 possiblities for a field, but I
> only need some of them)
Debra Dalg...PivotTable canned functions
I am using Excel 2000 (9.0.2720).
In my work I often faced with the task of creating ratios of
CurrentListValue / CurrentSaleValue then need to perform various
calculations on those ratios to identify and conclude on Central Tendencies
(among other things) within categories from a parent array. The list of
functions available for PivotTable calculation (I am assuming under the hood
these are Array Functions) is very narrow. Only providing for Average,
Standard Deviation, Min, Max, etc.
Is there a way to add to this list, such as but not limited to, Average
Deviation, Median, Geometr...PivotTable ?
I want to ask a general question ? What is the use of a pivottable ? Why is
it called "pivot" ? Actually, I can do the analysis on the original table
On Mar 13, 1:52=A0pm, "Carlson" <davidcarl...@yahoo.com> wrote:
> I want to ask a general question ? What is the use of a pivottable ? Why =
> it called "pivot" =A0? =A0Actually, I can do the analysis on the original=
a Pivot table is a very useful tool to analyse data columns. The name
stems from the technical field: a pivot...Multiple Sub Domains with own Catch-All Mailbox?
I have a client who wants to create multiple email sub-domains (no problems
settiung that up) but then has requested that each sub-domain has it's own
catch-all mailbox for unresolved emails.
Anyone have any pointers?
On Tue, 11 Jan 2005 05:21:04 -0800, Andy H
>I have a client who wants to create multiple email sub-domains (no problems
>settiung that up) but then has requested that each sub-domain has it's own
>catch-all mailbox for unresolved emails.
>Anyone have any pointers?
>...Link two subforms in datasheet view (one sub-form, one sub-subform
I’d like to know if this is even possible. I have a form named “Frm
Volunteer Opportunities” with a subform named “SubFrm Volunteer
Opportunities”. The subform must remain in datasheet view and has fields
like: [Event ID], [Event Date], [Start Time], [End Time], etc... I want to
link another form (a sub-subform?) to the datasheet so that whenever my
curser is located in one of the datasheet rows, this other form will present
all volunteers scheduled for that event. Then, when you move the curser to
the next row, those names would change. I’m guessing this means that the
m...Database Query #3
I posted this earlier with no replies. Anyone got any ideas?
I am using a database query to retrieve data from another database excel
file. The problem I am having is that the column of data which I have
contains records which have both text and number formats. When the data
transfers, it only brings those records which are number format. Is there
any way to make is so that the database query will pull both number and text
formats, as I can't easily change it so that it is one or the other? Thanks
The following article in Dick Kusleika's web log may help:
http://www.di...Query based upon another query not returning decimal data
I have Qry2 based upon Qry1 and that query is based on a linked table in SQL
2005. Fld1 and Fld2 are in the table and defined as decimal(28,18) in SQL.
Qry1 returns records containing Fld1 & Fld2 without any problems.
Qry2 is a Crosstab query performing a Group By / Row Heading on Fld1 and a
sum value on Fld2.
Qry2 gets an error on Fld1 stating "Invalid Precision for decimal data
type". If I use the CDec function on it, the error for this field does not
appear any more - but then after some processing, I get this error on Fld2:
"Invalid scale for decimal data type&...Pivottables
Is it possible to use a pivottable to comapre 2 data
sources and produce a pivottable of variances? Is so How?
This may work: I've copied|pasted one set of data under the other.
Then add an additional column to serve as an indicator (old or new).
Then created the pivottable with that indicator as a column field. Then I'd
usually convert to values and add a few formulas to look for differences. Apply
Data|filter|autofilter to find them.
Another option that may not work.
I've changed the values of the "new" data to negative
put -1 in an empty cell and...webquery function
I am using since a while the webquery possibility. The thing I can't fix is
I use the macrorecorder to record my functions, to process the same later.
My first step: start recording
My second step" Data > External Data > New Web Query...
search my webpage and enable the part I want to have in Excel and click
until now nothing special
My third step: In the Import Data screen, I click on the Properties button
and disable the Save Query definition and click OK and OK again to get my
data into Excel.
Fourth step: stop the macrorecorder
The result i...