Use Pivot Table data in formula
I have a pivot table that changes based on the "Job
Number" value I choose in the PAGE section of the pivot
table. I have a formula elsewhere on the spreadsheet
that picks up the value from the "Amount" column of the
pivot table. Problem is that when I change the "Job
Number" in the pivot table the external formula gives me
the #REF! error because it is still looking for the data
from the previous job. Any way to change that external
formula to a relative formula so that it picks up the
current amount in the pivot table instead of erroring out
when I ...Pivot table #12
I have a user with a 45MB excel file and he is trying to create a pivo
table with it. When he does it gets an out of memory msg. But he ha
500MB of ram and a 2gig swap file.
What would cause this? IS there a limit on the size a pivot table o
worksheet can be?
Message posted from http://www.ExcelForum.com
You might be running into an excel limitation. This is from the help file "Worksheet and workbook specifications
Maximum number of items in a PivotTable 8,00
Maximum number of pages in a PivotTable 8,00
...Pivot Table Dates
I'm bringing in data from an external database & creating a Pivot table with
grouped dates (Eg months)
when this table is copied and the date grouping changed from say months to
qtrs it changes the original to qtrs as well
is it possible to avoid this happening without creating seperate workbooks
You can always create another Pivot table within the same workbook, but not
link it to the existing one, You will pay the price in terms of filesize,
but it will fix your problem.
Ken....................... Microsoft MVP - Excel
...Pivot Table question #6
Hi I have a pivot table with some data. When I
put in the one of the fields "Costs" in the data area ,
excel does a count of the costs or if I go
in options( Summary) it gives me choices for Sum, % of
and etc. But I just want the values of the costs to show
up in the pivot table data area but it does not give me
that option .. I dont want any summarizing ( COunt, SUm,
Min, Max ..etc)
It seems I cannot find how to do that.
Please help . Any suggestion is greatly appreciated.
Without knowing the specifics, it's hard to tell whether a
pivot tabl...multiple page fields in a pivot tanle
I am trying to have a pivot table react to a change in this dropdown
box. The user clicks on 2 groups of option buttons first but after the
code gets to this line "pfTYPE.CurrentPage = strPI(1)", it won't go any
further. This changes the first page field but won't go beyond this.
What am I doing wrong ?
Dim pt As PivotTable
Dim pfTYPE As PivotField
Dim pfRIM As PivotField
Dim pfSIZE As PivotField
Dim pi As PivotItem
Dim strPI(1 To 3) As String
Dim i As Integer
Set pt = ActiveSheet.PivotTables(1)
Set pfTYPE = pt.PivotFields("TYPE")
Set pfRI...Downloadable evaluation version of Outlook 2003
does anyone know if it possible to get a downloadable
> does anyone know if it possible to get a downloadable
Not full Outlook, only Outlook Express is downloadable
along with IE...
>does anyone know if it possible to get a downloadable
...Evaluating text as formulae
Is it possible to refer to text in a cell as though it were a formula, ie
execute it instead of displaying it? Eg if the result of your formula was
"A1 + B2", to actually add A1 and B2 and display the result?
Like concatenating text?
=concatenate(a1," + ",b2)
"Nigel Ramsden" wrote:
> Is it possible to refer to text in a cell as though it were a formula, ie
> execute it instead of displaying it? Eg if the result of your formula was
> "A1 + B2", to actually add A1 and...Have you evaluated...
Has anyone evaluated Celerant Command Retail or Tomax Retail.net POS
solutions? How are they compared to RMS in terms of features and prices?
...A get/change first operation on table 'ASI_MSTR_Explorer_Favorites' cannot find the table
I am currently doing a server migration from two servers (following the
detailed KB steps) and am experiencing the error:
A get/change first operation on table 'ASI_MSTR_Explorer_Favorites'
cannot find the table
Here is the scenario
Server OS: Windows 2003 Server
DB: SQL 2000 with Reporting services (both OS and DB have updated
Number of GP Companies: 40
- I do not get any ASI errors and there is no issues whatever user I
use including the SA user
- When I log onto Client Workstation J, i also do not get the err...Get Pivot Table Data Formula
Is anyone a Pivot Table Guru?
I need to use the GetPivotData formula to get a subtotal line within the
Pivot, but when I use the equals I keep getting a reference error!
eg. Need to access Sep Hrs - cannot get total as I receive a ref error.
But for Total hrs I can use the formula!!
Is there a way I can use the getpivotdata formula or another formula to
access these category subtotals from the pivot table!! The pivot table is
dynamic aswell, and its range grows as you add in additional weeks..
There are example GetPivotData formulas in Excel's Help. Can you base
your fo...What table....
What table does the "More addresses" section use? The "More addresses" is
found on the side when you are filling out a contact.
"GDaxon" <GDaxon@discussions.microsoft.com> wrote in message
> What table does the "More addresses" section use? The "More addresses" is
> found on the side when you are filling out a contact.
Check Customeraddressbase. I hope this will help u.
"GDaxon&qu...Pivot Table Request
I tried to make a Pivot Table do the following:
"Show me the right value - copy the value to a Temporarily worksheet"
I tried the code shown below. How ever it keeps on giving me the same error
ColumnFields:="Activity", PageFields:="ARF Code"
Does someone see the error? Thanks in Advance!
Dim cLastRow As Long
Dim i As Long
Dim iPos As Long
k = Sheets("LookUp").Range("AM1").Value
Windows("Kostenbeheerssysteem.xls").Activa...form creating new row in table1, looking for field value in table
Please can someone help as I have been battling all week with this problem,
and haven't had a response.
I have a form which uses some data from table1 to create an entry in table2
(i know duplication is bad, but as this is a live database, and i am working
on a new section, this makes this complicated task easier), which works fine.
however, i have a third table that has the unique ID from table1 and table2
in order to show when a table1 row of data is needed in table2 via a
checkbox. This third table3 is created when elsewhere.
The problem lies with bringing in the unique ID value...Pivot Chart Legend editing?
How can I edit the legend in a Pivot Chart in Excel 2007? Or, if thats not
possible, can I edit the data titles in the values field?
Ex: the field is titled Amount Awarded. In the Pivot Field List-Values it is
"Sum of" or "Count of Amount Awarded". How can I edit out "Sum of" or "Count
of" so it doesn't appear in the chart?
On your pivot table, edit the data field heading, and change the name. Since
"Amount Awarded" is already reserved as the name of the data, you'll have to
cheat and make the name something like "A...Integration manager script for exchange rate tables
I am uploading journals into GP via IM. Does anyone have a script that will
apply the exchange rates from the GP rate table to my integration? Or is
there a better way to do this?
...Evaluate a Range
I would like to create a formula that looks at a range with a specified
criterion and when it finds the first entry in that range that meets
that criterion returns the position of that data in the range. For
example, given the following data beginning in A1 and extending to D1:
I would like to create a formula that looks for a value greater than 10
in that range and then returns the number 4 to signify that it is the
fourth entry in that range. Thanks as always for your help.
Message posted from http://www.ExcelForum.com/
Array enter (CTRL-SHIFT-ENTER or CMD-RET...Autoupdate Pivot Table for New Range
I have a 50-sheet workbook. The first sheet contains a
long list (>40,000 rows) and the next 49 sheets each
contain a pivot table that collects data from the first
Each month the number of rows in the list changes as rows
are added and deleted. Each month I have to update the
pivot tables. How can I do this without having to use the
wizard and manually drag through the new list?
If you use a dynamic formula to name a range, then use that as the
source for the pivot table, the new data will be included when the pivot
table is refreshed.
There are some instructions here:
...Hide System Fileds in table
How can I hide/show the system fields in a table i.e. s_Generation and
You posed your question in a newsgroup dedicated to Access forms. I'll
assume you want not to see those fields in your form, rather than "hiding"
them in the underlying table. After all, you don't want your users mucking
about in the tables directly...
If you need/want the fields attached to the form, but not visible, you can
set the control's .Visible property to False.
Microsoft Access MVP
Disclaimer: This author...linking tables for maximum efficiency
I am trying to build two tables containing information about fish that we
FishPK (one for each individual fish; autonumber)
CollectionPK (links to tbl Collection which contains information about where
and when we collected the fish)
The chemical analyses are only performed on a subset of the entire fish
I am pretty happy with this design, but am open to suggestions! In
particular, I want to know if this design allows me...Pivot table
I would like to sychronize two pivot table using different
data but having identical rows and columns using VB. The
result of the second pivot table will be based on the
selections of the field ((date) values in one of the
dropdown list of the first table.
Can anyone help?
You can use the following code, adapted from a posting by Robert
Rosenberg. It changes all Pivot Tables if the page is changed on the
first PT. You could revise it to suit your layout. As noted in the code,
place the code on the module for the worksheet which contains the first
Dim mvPivotPageValue As ...How to protect the query use for a Pivot that uses 'Get External D
I'm geting external data for a Pivot Table using the 'Get External Data'
option, this works fine and retrieves the information from a SQL 2000
In order to don't retrieve "all" the records, I use a filter (ie. where
Company equal to Microsoft), that way the User only sees that amount of data
that he is supposed to see.
The problem I have is that a power User that knows excel will be able to
edit/remove that filter and see "all" data.
Of course I can create different database views (ie. one for each filter)
and apply proper access right...tables and protection
We have created some tables- with 7 columns and 2 rows. One row is a title
row and the next row is a continues - some table over 3 pages in length. we
want the users to fill in 3 columns-- lining up with the information in the
other columns. when I put a password protection on so they can only type in
the 3 columns-- it slows down to a near halt.
Is there a better way to set the tables up so when the the protection is on
it doesn't nearly stop?
Should we use a different program for this?
If the information needs to line up horizontally, the...Copying table
How to paste a table with the Thunderbird mail to excel to keep the
> How to paste a table with the Thunderbird mail to excel to keep the
You may have better luck pasting into Word and then copying
and pasting into Excel.
daro wrote on 3/4/2011 :
> How to paste a table with the Thunderbird mail to excel to keep the
You could save it as a plain text file then import it into Excel as
Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic....How do I create a pivot table if the pivot table icon or menu ite.
If rhe pivot table icon ...??? Please clarify in the body of the message.
"Lynn@WS" <Lynn@WS@discussions.microsoft.com> wrote in message
...Relationship Help with Tables
This is the tables I have on my database. Can someone take a look and make
some recommendations on the relationships.
tbl FV Case Info = main (parent table - Id No is the primary key)
tbl FV Charges = child table (links with Id No to Case Info)
tbl FV VW = child table (links with Id No to Case Info)
The Docket Number = one case, each record should have a charges reecord with
count number, charge code, crime name, statue, disposition per count,
sentencing per count (there can be more than one count per case info record).
Then there is one V/W Record for each case (only one r...