need help with pivot tables

i'll try to explain the best i can here:
i have 3 sheets, 1. table with my quantity info, 2. (source data) table
1 transposed, 3. many "mini tables" referencing sheet 2
i first setup my pivot tables and got the "mini tables" setup the way i
needed. then of course quantities changed, then after updating sheet 1
and copying a transposed version to the second sheet and deleting and
adding rows and columns (so the headers will work for pivot table) data
dissappears from my pivot tables. what am i doing wrong? is there a
better procedure to accomplish what i need.
thanks ahead of time for any info
jeff
jpurl@nospamkeys-shehan-engr.com

0
jpurl77 (1)
12/21/2006 9:19:51 PM
excel 39879 articles. 2 followers. Follow

1 Replies
244 Views

Similar Articles

[PageSpeed] 58

Sheet2 should not be a manual process.  If you can make it a reference to 
Sheet1 with the sorting, formatting, and limitations you want, then your 
pivot table won't get confused because of inserting/deleting columns and 
headers.

If Sheet1 has all your data, and Sheet2 is a stripped down and organized 
"summary" version of Sheet1, just use some lookup or index/match formulas to 
set up Sheet2.  Then Sheet2 will never be manually touched again and your 
pivot tables won't get confused.

Honestly, you should be able to use the pivot table functionality without 
that middle step of a Sheet2.  You can tell the pivot table exactly what 
headings and rows to use.  Pivots are almost always done off of the raw 
data, not off of summary, stripped down data.  Heck, the whole point of the 
pivot table is to give you a summary, stripped down version of huge amounts 
of raw data.

I have a large database import I run monthly that I apply one filter to 
(remove all records with zero dollar values) and then I copy the dataset 
into Sheet1 of a workbook template I made.  Sheet2 is automatically set up 
to run a pivot table off of columns A through G from Sheet1 and then I have 
Sheet3 compare each client budget from another workbook to the actual 
revenue we've received YTD according to the pivot table.  I just use a 
simple vlookup to accomplish that.

Keep things as automated and flexible as possible and you shouldn't have any 
troubles.  If I had my pivot table run off of Sheet1!A1:G10000 and come to 
December to find we have 12,000 revenue entries, my pivot table data will be 
wrong.  This may or may not answer your question, but from the sound of it 
you need to think through your design process a bit on this project.

<jpurl77@hotmail.com> wrote in message 
news:1166735991.761820.236790@n67g2000cwd.googlegroups.com...
> i'll try to explain the best i can here:
> i have 3 sheets, 1. table with my quantity info, 2. (source data) table
> 1 transposed, 3. many "mini tables" referencing sheet 2
> i first setup my pivot tables and got the "mini tables" setup the way i
> needed. then of course quantities changed, then after updating sheet 1
> and copying a transposed version to the second sheet and deleting and
> adding rows and columns (so the headers will work for pivot table) data
> dissappears from my pivot tables. what am i doing wrong? is there a
> better procedure to accomplish what i need.
> thanks ahead of time for any info
> jeff
> jpurl@nospamkeys-shehan-engr.com
> 


0
12/21/2006 9:40:13 PM
Reply:

Similar Artilces:

How do you combine two columns to one? Help!!!!!!
I have address numbers in column A and street names in column B, I want to combine all of both columns into one without going through the whole process for each individual cells. HELP!!!!!! "sttrumpet" <sttrumpet@discussions.microsoft.com> wrote in message news:F72E43CE-CA99-4CD0-BCA1-85EFADCB459D@microsoft.com... > I have address numbers in column A and street names in column B, I want to > combine all of both columns into one without going through the whole process > for each individual cells. HELP!!!!!! Use the fuction =A1&B1 in cell B1 Drag the function ...

VB Code help
Here is a portion of one of my VB Macros in Excel: If Selection.Count > 0 Then MsgBox ("Average = ") & (RunningTotal / Selection.Count), vbInformation, "Average" End If My question: How do I format the number (RunningTotal/Selection.Count) to look like 0.000, instead of 0.0000000000000000?? What should my code look like with the new formatting. Thanks Chris Chris, Try this: ....Format((RunningTotal / Selection.Count), "0.000")... -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- &qu...

Help with formula #18
Hi All: I am WAY over my head here, and you all have always been great help, so hopefully someone can help with this. I had someone write this for me to use as an index at the top of a spread sheet. It works beautifully . The problem is I would like to move this index to sheet 2 of the same spreadsheet and when I copy and paste it, it does'nt work. Is there anyone who can modify this to work as an index in sheet 2 and will search sheet 1 for the results? TIA Keith =IF(#REF!="","",IF(ISNA(MATCH(#REF!,$H$31:$H$65536,0)),"",HYPERLINK("#"&C...

Help needed with Money 2003 files
Hi there, I've been using money 2003 for years even when upgrading to new PC's as I had the original disk. I've just had to rebuild my hard drive and now cannot find the disk anywhere so cannot install Money. As you cannot now buy Money, I've tried downloading the trial version of Money so that I can access all the account details/balances but it says that they're not compatible with earlier versions of money. Is there anything that I can do to get these files open or have I lost all my records for the last 6 years? I'd really appreciate your assistance. Jayne EggHead...

How to change pivot table source database?
How can I change the source database for a pivot table in an Excel spreadsheet. Our database server has been changed and when I create a new dsn pointing to the correct server, I get an error message indicating that the query on the old server is still trying to be used. Thanks, Steve This should work for you (?) Open the pivot table and click on it. Menu - Data/Pivot Table report. Click the Back button on the Wizard dialog that appears and connect t the new data source -- Message posted from http://www.ExcelForum.com ...

refresh pivot tables
if i have my pivot table not set to refresh on open. is there a way to refresh all of them at once. when i want them to be refreshed? does F9 do this? from the help, F9 says this: Calculates all worksheets in all open workbooks. F9 followed by ENTER (or followed by CTRL+SHIFT+ENTER for array formulas) calculates the selected a portion of a formula and replaces the selected portion with the calculated value. SHIFT+F9 calculates the active worksheet. CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation. CTRL+ALT+...

Diagonal Pivot Table?
Hi, I currently have data that looks something like A | B | C 1 | 1 | 5 1 | 2 | 9 2 | 1 | 7 2 | 2 | 4 (but obviosuly many times bigger) If I put this in a pivot table, with A and B on the axis, it would loo something like: _ | 1 | 2 1 | 5 | 9 2 | 7 | 4 This is because it counts (1,2) as different from (2,1). Is there an way to concatonate the two? i.e.: _ | 1 | 2 1 | 5 | 2 | 8 | 4 Let me knwo if this doesn't make sense. Thanks -- Message posted from http://www.ExcelForum.com Wait, cancel that! Turns out I don't need to worry. thanks anyway, - -- Message posted from http://...

how to jump from form to related record in table
I am trying to deal with the limits on updating fields in queries. So, in a form that shows data re orders waiting to be shipped, I'd like to be able to doubleclick on the order_ID number field (let's say order # 02975) and have that cause a related form or table to open at record number 02975. Then, I can directly edit the data that needs editing. Using Access 2000 "Macro builder" and the On Dbl Click event, I have been able to get a desired form to open and have the focus, but I cannot get the related record to be selected. (I'd prefer selected to filtered.) I am ho...

Need help with using a bar graph in a report
I have a report that separates information about representatives by manager. I need the graphs to give a quick summary on a few stats from each rep but only show for that manager's team and not all information. For example: Manager A has RepA, RepB, and RepC under him. Each rep has Aux1, Aux2, Aux3 information which needs to be displayed. My goal is to show Manager A with his Reps A,B, and C of their Aux1, 2, and 3. Then, under the next manager Header I want Manager B to show Rep D, E, and F with their Aux1, 2, and 3 information. Etc thru Manager G. Please help... Th...

How to reach through subform to underlying table?
With VBA I've learned to pull data from fields on my subform like this: sbfVendorDetail.Form.[Start Date]. However, fields such as the ID may not be on the subform since the user doesn't need to see it and it would take up space. Is there a way to read the ID field of the current record of the subform without putting the field on the form? "WDSnews" <wdsnews.0640@oregoncity.com> wrote in message news:eQAVmQ$5KHA.980@TK2MSFTNGP04.phx.gbl... > With VBA I've learned to pull data from fields on my subform like this: > sbfVendorDetail.Form.[Sta...

Help with Userform
I need to setup a restricted means of allowing users to edit data in existing cells on a spreadsheet through a macro (userform?). I want to create a userform that will read data from these existing cells and display their contents in a series of text boxes. The user will then have the choice to edit any data item, if they need to, or leave the data as is. Upon the user hitting the OK key on the userform, the edited data will be entered in their previous cell locations. Any help or examples will be appreciated! Thanks ...

Lookup table oddity
Hi I have a lookup table that was in an assessment exercise and it refuses to work on one cell. There are two lookup tables in the exercise... one looks up the product code - this one works okay. The one that won't work properly is the second one, which uses the supplier as the lookup value. I tested it out on a computer with 2003 at the training centre where I work and it worked. However, it won't work on my 2007, or any of the computers using 2007 that I tried it on. I can attach the file for anyone who is interested in looking at this oddity. It is driving me distracted...

populating from sheets -- and PLEASE don't tell me I need VB!
Stupid newbie question, Excel 2003: I have a 2 sheet document. SOME of the info from Sheet 1 needs to appear on Sheet 2. For example, Sheet 1 Column G values need to be populated to Sheet 2 as Column B, Column H needs to be Column D, etc. Is there any way to automate this without having to write a VB script? I know a macro could kind of do it, but I don't think it could take all the parameters, since Sheet 1 is constanly being altered (i.e., new values, etc.). Oh, and all rows are relational so that G3 value from Sheet 1 when it goes to Sheet 2 B17 also has to have H3 from Shee...

need inv/rec/issue template
I need an Excel template that can manage reciepts and issue and track material through multiple step projects. I know that it will be input intensive but we are small making onesies and twosies with multiple inside and outside processes. Thanks for your assistance Mark This sounds like a lot more than just a template, Mark. Sounds like a complete custom application to me. The best I can do is an invoice and sales tracking method in my downloads folder at www.officearticles.com/downloads/ ************ Anne Troy www.OfficeArticles.com "Material Maven" <Material Maven@discu...

amend a PO with a query? Please help!
Hello, Our new warehouse manager entered, received and committed a PO from the wrong vendor - over 100 items! Does anyone know a way to either reverse the PO or change the vendor? Please help!!!! Thank you! diana Diana, You can reverse the PO by opening it again in receiving mode. Use the Quick Scan button and add an item to the PO (any item). This will 'unlock' or re-open the PO. You can now delete the item you just added back off. Next you would enter the quantity 'Received to Date' of each item into the 'Quantity Received' column as a negative. Fo...

Please Help...I can not send/receive or delete email on outlook 2000
It gives an the following errors" Error has been detected in the file C:|Windoews\Outlook.pst. quit all mail applications and then use Inbox Repair tool." I spent hours tring to find a solution on the support section. Couldn't find it.. Thank you so much for help.. What size is yr *.pst? OL prior to 2003 can have problems when greater than 1.7gb The inbox repair tool is scanpst.exe If oversize; http://support.microsoft.com/?kbid=296088 David "Daniel" <dqiab@yahoo.com> wrote in message news:05a401c3676d$0ef74c50$a401280a@phx.gbl... > It gives an the follow...

line graph-need to pick up dates as values for the x axis?
How do you get a line graph to pick up and show dates as the values on the x axis, not starting at zero nor at the first of the month. There doesn't seem to be anywhere for x-axis data to be transferred to the x- axis. I did put the dates into a row of cells and highlight them. i am using MS office 2000. Go to Source Data on the Chart menu. On the Series tab, select the series, then click in the XValues box, clear it if necessary, and select the range that contains your dates. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions ht...

Need help with setting IFrame script
I got the perhaps overly ambitious idea of customizing the Account screen to load the map and directions from our office to the customer site. I am using the DirectionsFind url, though I may switch to MapQuest if I can find the parameter list. Which I use, however, is a moot point if I can't figure out how to make it work in an IFrame. I have written and debugged the JavaScript that concatenates the http command for generating the map and directions from one point to the next. I know the command works because I've pasted the results into my browser and it brings up the corre...

help, derag won't work and the outlook folder shows 36000 fragmnets
defragmented and the program works fine but when it finishes it looks the same on the as the original analysis. Then I look at the report and it list a lot of files that can't be drfragmented. The only thing I can think of is that this pc was once on MSexchange (the reason I mention it is that the outlook pst which is 1.8 gigs, shows 36000 fragments and it is located in the c/exchange folder) Anyway, what do I do??? how can I get the drive to defrag? Hello Bob When you open IE\Properties\Programs what do you see in there under E-Mail box. Use the dropdown arrow and take a peek on ...

Please help
Only one of my users (who us utilizing IE8) is getting the following error when creating a new Timesheet in PWA 'g_oMainForm_name' is null or not an object mytssummary.aspx Code: 0 URI:http://[project server]/mytssummary.aspx Many other folks are using Timesheets - some with IE8 - and it works fine. This is the only user so far who has reported this. Any help would be greatly appreciated. Thanks! Andy Novak UNT Hi Andy, Maybe this will help: http://allfaq.org/forums/t/136987.aspx "anovak@unt.edu" wrote: > Only one of my users (who us...

Help in macro in excel.
Hi all. I want to write a macro in an excel sheet wherein I want to copy a few cells from my source worksheet and paste their hyperlink to another worksheet. Also, the destination worksheet's cell number where this is pasted is not fixed. How do I accomplish this? Please help urgently. Thanks. SPG, Running the macro recorder whilst inserting a hyperlink yields this code: ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "Sheet1!A1", TextToDisplay:="""go to hell""" Now just substitute the relevant stuff into e...

HELP PLEASE!!! #2
This is a multi-part message in MIME format. ------=_NextPart_000_0018_01C5D651.83566720 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0019_01C5D651.83566720" ------=_NextPart_001_0019_01C5D651.83566720 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable how do i get this background off this?? im very new... thanks.... =20 ------=_NextPart_001_0019_01C5D651.83566720 Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-/...

Help creating a list
Hi, I have a list of values in a single column. Many of the values are repeated. I need to create a list from this, where each value only appears one time, and capture in a new tab. Example Tab 1: 2008 Record A 2008 Record A 2009 Record B 2010 Record B 2010 Record B Result Needed in Tab 2: 2008 Record A 2009 Record B 2010 Record B Appreciate any help....Thanks ...

my excel does not work properly, please help !!
hi there, i got all the Microsoft office programs on my laptop. Excel use to work perfectly, till last week or so. when i open it or open an excel program; it opens as if its " INTALLING " excel. it gathers information to install it then it says File could not be found on C:\Program Files\ then it asks me to BROWSE to look for the file. please help me, i need excel to work very soon. aa p.s. i had the office CD back home .. im in another country for couple of months. and you know how much it costs to buy a new 1. help me .... ...

Formula you typed contains an ERROR message
Hi Everyone - I am trying to enter the following formula: ***************************************************************** =IF($C7="SIL",0, IF((AND(OR($D7="Whole Shelled",$D7="Ground Shelled"),$R7<15.5,$R7>=0)),1, IF((AND($D7="Ground Cob",($R7-5)<15.5,$R7>=0)),0.5165, IF((AND($D7="Whole Shelled",$R7>=50)),0.5572, IF((AND($D7="Ground Shelled",$R7>=50)),0.6288, IF((AND($D7="Ground Cob",($R7-5)>50)),0.3016, IF(AND($D7="Ground Cob",$R7-5<=50,$R7-5>=15.5), INDEX(Tables!$A$131:$D$166,MATCH(($...