+ Reply to Thread
Results 1 to 5 of 5

Using Name Manager named ranges against a pivot table output

  1. #1
    Registered User
    Join Date
    04-18-2017
    Location
    Luton, England
    MS-Off Ver
    10 & 13
    Posts
    3

    Using Name Manager named ranges against a pivot table output

    I'm new to forums and haven't posted anything before.

    I have created a pivot table from a large range of data which includes Region, Customer Name, P&L Level, Products and then the total amounts for 2015, 2016 & 2017. From this I need to do more analysis, so I have used the Name Manager to create named ranges for the Customer for each Region and amounts by Region for each of the years. This means that I have 4 named ranges for each Region. The formulas work when I have the full data showing in the pivot, but when I filter on one of the P&L levels, the Regions inevitably shorten. I have worked around this by redefining the ranges as to what is now displayed, but this then needs be repeated for each P&L level, making this a very manual process and open to errors. The usual amount of data I am working with consists of over 255k lines and is ordered by date. This needs to stay ordered in this way, as there are other formulas within this data which won't work if the data is rearranged. The pivot table usually covers over 100k lines of data.

    The attached shows a file of test data similar to the data I am using. The first pivot shows the named ranges and the second pivot shows the named ranges when the pivot is filtered.

    Is there any way to make the named ranges dynamic and so change whenever the pivot is filtered in any way? I am using Excel 10 but also have Excel 13.
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Using Name Manager named ranges against a pivot table output

    The key is in the offset command.

    The basic offset command has 5 arguments:
    -Start Cell
    -Rows to go down (or if negative, up)
    -Columns to go right (or if negative, left)
    -Rows to return
    -Columns to return

    So OFFSET(A1,1,2,3,4) means
    start in cell A1
    go one cell down (to cell A2)
    go two cells to the right (to cell C2)
    and return a range 3 rows deep and 4 cells wide
    In other words, Range C2:F4

    Any of the 5 parameters can be calculated using COUNTA, COUNTIF, MATCH or other formulas.

    In the attached example I calculated a named range called EMEA_Main =OFFSET(pivot!$A$1,MATCH("EMEA",pivot!$A:$A,0)-1,0,COUNTIF(pivot!$A:$A,"EMEA"),1)

    Start in cell A1, find the row where EMEA is first found (subtract 1 because Match starts counting with 1 whereas Offset starts counting at zero), go right zero columns and give me a range COUNITF() rows deep (the number of EMEAs we have) and one column wide.

    Once you have a named range established, you can define other ranges by using offset from the known range. In this case there are only three parameters: OFFSET(NamedRange, RowOffset, ColumnOffset)

    So I defined EMEA_15 =OFFSET(EMEA_Main,0,MATCH("Sum of 2015",pivot!$3:$3,0)-1)

    Which is the same range as EMEA_Main shifted down zero rows and shifted right by MATCH()-1 columns.

    Similarly, I defined EMEA_16 and EMEA_17.

    This should be enough to get you started with the rest. Bear in mind that as you make data "disappear" some of these ranges may point to nothing.

    For more information on Named Ranges, see this article: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    04-18-2017
    Location
    Luton, England
    MS-Off Ver
    10 & 13
    Posts
    3

    Re: Using Name Manager named ranges against a pivot table output

    Thanks for your post but I can't seem to get this to work.

    The ultimate aim of the exercise is to identify where I have a brand new customer or have completely lost a customer within a Region. There are another formulas not shown (not using the named ranges) which identifies if there is a new product but against an existing customer. The aim of the named ranges is firstly to identify the customers within the Region - EMEA range covered B11:B26; then their values in the respective years - EMEA15 range covered D11:D26; EMEA16 range covered E11:E26; EMEA17 range covered F11:F26. The formula to identify if it is a brand new customer was in the lookup beside the pivot in column New customer in 2016 e.g.=IF(AND(SUMIF(EMEA,$B11,EMEA15)=0,D11=0,E11>D11),"New Cust","-") with the lookup in Lost customer in 2016 e.g.=IF(AND(SUMIF(EMEA,$B11,EMEA15)>0,SUMIF(EMEA,$B11,EMEA16)=0,E11=0,E11<D11),"Lost Cust","-"). This is then repeated for New & Lost in 2017 (not shown). When I filter the pivot on just one P&L Level e.g. Data1, the EMEA range inevitably shortens & moves to rows 6 and 7 rendering the initial ranges above useless. I then have to create a new named range for this shortened version again for each Region, which is open to errors.

    I'm not sure whether these ranges stayed with the spreadsheet when I uploaded it, as they had disappeared on the copy lodged with the reply. Perhaps desensitizing the data hadn't made my intention clear.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Using Name Manager named ranges against a pivot table output

    Here is a different kind of logic. See the pivot table on the data sheet.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-18-2017
    Location
    Luton, England
    MS-Off Ver
    10 & 13
    Posts
    3

    Re: Using Name Manager named ranges against a pivot table output

    Thanks for this. It works better than my named ranges, however it is now repeating the values for e.g. Customer B has 2 products but is repeating the same values for both products. I have got around this by adding Table_Data[Product] at the end of each formula. Thanks for your help with this.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 01-24-2017, 04:10 PM
  2. [SOLVED] OFFSET COUNTA Named ranges in a table
    By dma1976 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-27-2014, 10:36 AM
  3. [SOLVED] Create code to update named ranges in a table. Table contains Many Name Ranges
    By Calio in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-09-2014, 11:13 AM
  4. Replies: 5
    Last Post: 08-22-2013, 09:56 AM
  5. Pivot Table Using Multiple Named Ranges
    By nathanB in forum Excel General
    Replies: 2
    Last Post: 11-26-2012, 08:59 AM
  6. Applying named ranges to Pivot table
    By RBI in forum Excel General
    Replies: 5
    Last Post: 10-07-2008, 01:28 PM
  7. Pivot tables & dynamic named ranges
    By WillysK5 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-05-2008, 04:32 PM

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