Advanced Excel Pivot Tables

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

viveks

Thread Starter
Joined
Sep 16, 2004
Messages
3
Hey,

Sorry this might not be advanced, but it is to me :(

I'm using pivot tables to analyze alot of data.
I wanted to perform calculations on some of the fields, and
contain this information in the report of the table.
Eg like get the percentage of a few columns?

If no one knows this, i was wondering is using VLOOKUP is the way to go?
and have the values say of the months figures being copied into a set report on another page. The problem with this was that if the pivot table changes then doesn't the whole VLOOKUP totally stuff up?

I'm looking into the new Cube Analysis upgrade for excel 2003, I'm not sure if that will solve all my problems but we will see

thanks
vivek.
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,468
you can add formulas into a pivot table as part of the table - then nothing varies.
although i nolonger have played with this - but we used to pull data into a spreadheet datasheet from SQL and that updated a pivottable which calculated utilisation percentages etc based on the pivot tables grouping.

can you post an example of what you want and maybe i can see what i can remeber
 

viveks

Thread Starter
Joined
Sep 16, 2004
Messages
3
Thanks guys,

the link was useful, i was think about macro's but have never done any macro coding. Now my main problem is that i've started playing with 'cube analysis' in excel and pivot tables at the same time.

cube analysis seems to do everything that a normal pivot table can, but with more. i currently dn't really know either of them that well :S

I was hoping if anyone could suggest which one i could go with:
An example of what i need to do is as follows:

I get a LARGE amount of data about all kinds of prices, costs from different areas, and i need to produce multiple reports. Some reports are 'Sum's' of
certian data, like the amount earned during january 2004 by one company.

The totals of each month are needed, then percentages against other company totals are needed. (That so far is o.k)

My problem lies, in the output report. Now say if i have 2 companies, i wanted an automated(as simple as possible) way that i could keep producing different reports, and hide other peices of information say a competing companies earnings for a period.

I found that i can get a data dump from the cube analysis, but i think the cube analysis can do the analysing for me, rather than using a pivot table.

Sorry i think im rambling, i can't describe my problem well. Its just that the pivot tables say, keep changing, when the data is refreshed, or depending on the company you are looking at, and rather than cutting an pasting the data into other sheets, i wanted to automate this process.

sorry, hope this makes sense to anyone...

viv.
 
Joined
Sep 17, 2004
Messages
21
Dear

I work quite a lot with Pivot tables and vlookups so I can only give you a reply there, If you use a vlookup you most likely use a reference that's fix in a particular column. meaning if you have in 2 sheets the same reference in cell A2 example Piet, but in the second sheet you have also the name with next to it his age adress and so on you can let vlookup do the work for you. just pay attention that within large files this takes quite a lot of resources of your computer so it's easier to sort them from A-Z, also make sure you use a unique reference otherwhise your data is corrupted since the vlookup will input the same data twice for the wrong person .

example you have Piet who is 25 and 2 lines below the first Piet you have a second but he's 23, the vlookup will always display 25 and not 23

hope this helps
with kind regards Paul
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top