Advanced Excel Pivot Tables

[viveks]

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.

 

[WhitPhil]

I can't answer the question specifically.

But, see if this tutorial points you in the right direction?

 

[etaf]

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]

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.

 

[paulus4605]

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