Posts by jacknijssen

  • Year To Date In PivotTable

    Hi,


    I have a financial model on a SAP Business Warehouse Query, which i derive via the SAP BW Business Explorer in Excel (named BEx). I derive the data from the query via the getpivotdata formula in combination with ranged names:


    For example


    In the Month : = getpivotdata(Pivot,concatenate("Philips Design"," ","EBIT PD"," ","Actuals"," ","Month"," ")
    In the Quarter := getpivotdata(Pivot,concatenate("Philips Design"," ","EBIT PD"," ","Actuals"," ","Quarter"," ")
    Full Year : = getpivotdata(Pivot,concatenate("Philips Design"," ","EBIT PD"," ","Actuals"," ")


    whereas Actuals/Month/Quarter are ranged names. For Month and Quarter i set the applicable month or Quarter that applies to the current reporting, eg. 012.2007 as month and 20074 as quarter.


    My concrete question is for the getpivotdata formula to derive the Year to Date figure. The month figures in the pivot table are in the month data.


    Ps: i would like to keep all formulas to this one pivot table, i know i can do it with a second one.


    greetings,


    Jack

  • Web Query Multiple Pages

    Re: Web Query


    Hi Thanks,


    i also saw below option in the help, i have excel 2003, but i do not see the options to refresh the data in the paste options. should i change any settings in excel?


    Copy data from a Web page
    Show All
    Hide All
    This procedure requires Microsoft Internet Explorer 4.1 or later.


    In your Web browser (Web browser: Software that interprets HTML files, formats them into Web pages, and displays them. A Web browser, such as Microsoft Internet Explorer, can follow hyperlinks, transfer files, and play sound or video files that are embedded in Web pages.), select the data you want to copy.
    On the Edit menu, click Copy. If your browser doesn't have this command, see your browser's Help for information on copying.
    Switch to Microsoft Excel.
    Click in the upper-left corner of the worksheet area where you want the copied data to appear.
    On the Edit menu, click Paste.
    If the data does not appear as you expect, click Paste Options and select one of the following options:
    Keep Source Formatting Makes no changes.
    Match Destination Formatting Matches the existing cell formatting.
    Create Refreshable Web Query Gives you the opportunity to create a query to the Web page you copied from. This gives you the opportunity to refresh the data should the Web page change later.

  • Web Query Multiple Pages

    Hi,


    I want to create a Web Query on a webpage which was created by a SAPNetweaver page.
    It all works, however excel only imports the first page. How can i connect to all pages?


    I use excel 2003


    thanks


    Jack

  • Index formula

    Re: Index formula


    Adding a 'key' for total as unique cell is not possible. This figure is not available in table. Table consists of individuel numbers per cost block. Via use of index formula the unique costblocks are shown. I now want to sum in the index formula all costblocks belonging to this unit to get total. Index formula standard stops when finding the first match in the table.

  • Index formula

    Hello all,


    In a reporting file I am using an index formula (=INDEX(DataRange, MATCH($AN12,PrimKeyReport,), MATCH(AP$8,RowRange,0)) to search my table till match in row and colums is a fact. Works fine. However, table is full of cost groups and if you put some groups together they form a cost unit.


    Now my questions starts : I would like to use the index formula now to find total amounts for costunits. I created a costunit prim.key by adding a costunit code to each cost group in my table. Problem: when selecting the cost unit the index formula 'stops' after finding the first cost group with the unit code. I would like to select the costunit with outcome of sum of all cost groups with this unit code. Anybody knows how to create a sum (cost unit) of (group costs) amounts combined index formula ?


    Thanks.


    Jack

  • Charts - Automatically sort data

    Re: Charts - Automatically sort data


    Attached is the illustration of my problem:


    The bars of the chart are not sorted. The best and worst performer cannot be spotted directly.




  • Charts - Automatically sort data

    Dear all,


    I've got a problem with a chart I'm creating in Excel. The chart I'm using is a 'bar chart', in which the bars in the chart reflect a certain percentage for 10 separate countries.


    The problem I have is: How can a chart automatically sort the values in the data I'm using? I want the percentages to be sorted as follows: the countries with the highest percentages must be at the top of the chart, while the countries with the lowest percentages must be at the bottom.


    The chart has to do the sorting, because it has to be applicable for hundreds of datasheets (I dont want to sort these sheets manually; it has to be done every month).


    Thanks in advance for you help!


    Greetz,
    Maarten

  • VBA Grouping

    Hi ....,


    I have a grouping questions,


    I wish to group column A:F and H:I, and not G
    when i use the following code also column G is grouped.


    Columns("C:F").Select
    Selection.Columns.Group
    Columns("H:I").Select
    Selection.Columns.Group


    Also when i group all and then ungroup G it does not work, any idea?


    Jack

  • VBA EXCEL - PPT Paste data to Table

    Hello,


    I would like to Paste Excel Data to PPT. I know how to do this, but i wondered if anybody had code available to do this to a table (instead of picture) in powerpoint.

    Eg: Cell A1 in a spreadsheet is the first cell of the table in ppt.


    The reason for this is that it is much easier to edit afterwards in ppt.
    It is a lot of work to paste as pictures, and like i said a table form is much easier to edit in ppt.


    thanks,


    Jack

  • VBA : Copying Sheets

    Hi,


    I have a workbook with 6 sheets.
    I make workbooks with one sheet
    and send them out seperately.


    Is it possible to have VBA code this,
    including naming the sheets and placing them in a certain dir. and possibly naming the sheets also?


    Jack

  • Pivot Tables: Scenario on Hierachic Pivot table

    Derk,


    I have been able to build a hierarchy into
    the scenario file ( see comboboxes next to
    pivot page fields in sheet one pivot )
    Thanks to Stephen Bullen.


    What i would like to do is to determine a level of hierarchy for which the pivot table scenario should be applicable.


    And i would also be able to set the double level. I thought myself by using the select from listbox option, see button
    display dialog.


    Briefly, i wish to choose what i scenario, not all page and field items should be multiplied with the scenario percentages, only the selections.


    Is this possible with the things i added ?
    Maybe by using calculated fields from the
    cells that the select listbox returns?
    I wish calculated fields in the end anyway,
    because i do not want to keep the scenario outcome as data in the named range database, because it will increase the amount of data too much.



    greetings,




    Jack

  • Pivot Tables: Adding Scenarios

    Derk,


    Perfect! Thanks a lot. I did not yet base the data on the real database, let me do that now. Further i can play with Calculated Fields.


    After that i will come back to the forum,
    the model is more concrete then.


    The following items i still had in mind:


    - Scenario on hiearchy ( so lets say i have a Business , A line of Business, Main Articels and Articles, i wish to scenario sales deviation on different levels. I will prepare some layout for that
    - Leave the scenario data out of the spreadhseet and calculate via calculated fields.


    See you later Derk, lets go to work and have a weekend!


    tchau


    Jack

  • Pivot Tables: Adding Scenarios

    Hi user,Derk


    I have now Pivot Tables with Scenarios
    included, what i would like is to set
    scenarios with the Comboboxes i added
    and to add them to the Scenario Datafile.
    Name them also.


    Further, a posibility ( listbox, combobox ) to delete from the options.


    greetings,


    Jack


    current scenarios

  • [Solved] VBA: Pivot Table Programming

    Derk,


    The way you propose i made it now,
    two different pivots, i can set the pagefields via the comboboxes, scenario via the combo boxes in the second pivot.


    But, like i said i would like to use one pivot table, because i do not intend to extract the raw data from an external database, so i would like calculated fields in the first pivot and switch between original pivot data and scenario pivot data and compare original data with the scenario for lets say Sales value etc.
    by including and excluding fields.


    As you can see the second pivot table changes with a different selection of a scenario.


    All by clicking for example a combobox, listbox, checkbox.


    greetings.

    Jack


    But further what would be welcome are ideas to make scenarios on pivot tables,
    because like i said it is extemely powerful.


    See adapted file enclosed.

  • [Solved] VBA: Pivot Table Programming

    Derk,


    I have in my head what i want, i do want to include the scenarios into calculated fields. In my end model i will have an ODBC link, so to include the scenarios into the raw data is not my preferred option.


    That is why i would like to use calculated fields.


    If you could get me going how i could use the scenarios on sheet Results : B10 to F10 into the Pivot Fields on sheet Raw Data C1:G1 i would be very happy.


    After that i will continue to develop from there.


    greetings,


    Jack