Dynamic Named Range From Pivot Table

**Lohann1974** · 05-01-2020, 12:23 PM

Hi,
want to create a dependant dropdown lists from the pivot table For categories and products.

How do i create a dynamic range that expand when ever categories is added?

=OFFSET(Sheet1!$A$2,1,0,COUNTA(Sheet1!$A3:$A200),1) is not returning the correct range

Any Sugestions?

**Eastw00d** · 05-01-2020, 03:21 PM

As far as I know you cannot create a Named Range from a Pivot, but I have a nice workaround for you:
a combination of the OFFSET function and the AGGREGATE function.
the last one in column "J":

Please Login or Register  to view this content.

and the OFFSET in the Named Range:

Please Login or Register  to view this content.

Cheers
Erwin

Dynamic Named Range From Pivot Table

LinkBack

Thread Tools

Rate This Thread

Display

Dynamic Named Range From Pivot Table

Re: Dynamic Named Range From Pivot Table

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Dynamic Named Range instead of Table for data transfer to userform listbox

[SOLVED] Named range and reference to one cell within dynamic table

Dynamic Named Range based on Pivot Table for MULTIPLE COLUMNS

[SOLVED] Dynamic Named Range based on Pivot Table

Combobox addition to Rowsource in dynamic named range corrupting table

Dynamic Named Range and Pivot Tables - Blank Cells

Why do I always get a blank row / column with a pivot against a dynamic named range?

Bookmarks

Bookmarks

Posting Permissions