Back to Blog
Step 4: Use the named range in formulas etc. The COUNTA($F:$F)-1 ensures that we get only row labels and not the title (in this case Product Names). The formula gives us all the values in column F, starting F6. =OFFSET($F$6, 0,0,COUNTA($F:$F)-1,1) and map it to a name like lstProducts. Assuming the pivot table row values start in cell F6, write a formula like, Using OFFSET formula, we can create a named range that refers to pivot table values and grows or shrinks as the pivot is refreshed. Step 3: Create a named range that refers to the pivot table values Make sure you have turned off grand totals and sub-totals as we just need the names. Very simple right? Step 2: Drag the field(s) to row label area of pivot Just select any cell and insert a pivot table. This is how it goes…, Step 1: Select your data & Create a pivot table It is no wonder that I have written extensively about it (here: 1, 2, 3, 4, 5, 6, 7, 8).īut today I want to show you a technique I have been using to dynamically extract and sort all unique items from a last list of values using Pivot Tables & OFFSET formula. Removing duplicate data is like morning coffee for us, data analysts.
0 Comments
Read More
Leave a Reply. |