1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Advanced Excel Pivot Tables

Discussion in 'Business Applications' started by viveks, Sep 16, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. viveks

    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.
     
  2. WhitPhil

    WhitPhil Gone but never forgotten Trusted Advisor

    Joined:
    Oct 4, 2000
    Messages:
    8,684
    I can't answer the question specifically.

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

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,294
    First Name:
    Wayne
    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
     
  4. viveks

    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.
     
  5. paulus4605

    paulus4605

    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
     
  6. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/274907

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice