+ Reply to Thread
Results 1 to 3 of 3

Pivot Table Field Settings

  1. #1
    Michael
    Guest

    Pivot Table Field Settings

    Is there a way to select the "Last" value using a pivot table?

    I have a spreadsheet with dated sales data. This data is sorted by date.
    When displaying the data in a spreadsheet I would have the following columns:

    item, item_description, price, qty_sold, sale_amt

    The qty_sold and sale_amt are sums.

    I would like the price to be the last price found in the "datasheet".

    Is this possible.

    Thanks in advance.

  2. #2
    Michael
    Guest

    RE: Pivot Table Field Settings

    Actually what I didn't do a very good job of explaining my situation.

    My data sheet contains all sales over a period of time. I am using the
    pivot table to consolodate information by item description etc. Each time a
    product changes price during that time frame the pivot table shows all unique
    values. Under field settings I could choose one of the following: Sum,
    Count, Max, Min, Avg etc. I am looking for something that will show me just
    the last value. I know in SQL you can do that I am just wondering if I can
    acomplish the same with a pivot table.

    Example would be I sell a widget for 23.00 9 times in January and February
    but in March my price goes to 22.50 and I sell 2. Currently my pivot table
    summarizes data for the $23.00 and the $22.50 prices showing the correct
    calculation. I could choose average but that doesn't give me the current
    price. If I sort my datasheet by date selecting the "last" value would give
    me what the most current price was.

    They don't always go down so I can't use "Min" and the same is true with
    "Max".



    "frankt" wrote:

    > Does it have to be a PivotTable?
    >
    > Range("C65536").End(xlUp).Select will find the last used row in column C.
    > This presumes Price is in column C.
    >
    > "Michael" wrote:
    >
    > > Is there a way to select the "Last" value using a pivot table?
    > >
    > > I have a spreadsheet with dated sales data. This data is sorted by date.
    > > When displaying the data in a spreadsheet I would have the following columns:
    > >
    > > item, item_description, price, qty_sold, sale_amt
    > >
    > > The qty_sold and sale_amt are sums.
    > >
    > > I would like the price to be the last price found in the "datasheet".
    > >
    > > Is this possible.
    > >
    > > Thanks in advance.


  3. #3
    frankt
    Guest

    RE: Pivot Table Field Settings

    Does it have to be a PivotTable?

    Range("C65536").End(xlUp).Select will find the last used row in column C.
    This presumes Price is in column C.

    "Michael" wrote:

    > Is there a way to select the "Last" value using a pivot table?
    >
    > I have a spreadsheet with dated sales data. This data is sorted by date.
    > When displaying the data in a spreadsheet I would have the following columns:
    >
    > item, item_description, price, qty_sold, sale_amt
    >
    > The qty_sold and sale_amt are sums.
    >
    > I would like the price to be the last price found in the "datasheet".
    >
    > Is this possible.
    >
    > Thanks in advance.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1