Dynamic Named Range based on Pivot Table

[Pho6]

Is there any way to update the ranges in the "Named Ranges" section so that if a new value inserts, it would automatically update the "Named Ranges"?

For example, current "Named Ranges" under "Colour" includes A5:A16. Any way that it could automatically update the named range "colour" from A5:A17 if a new value is updated in the Pivot?


See attached sample file please! VBA or non-VBA solution would be helpful.


Thanks!

Pho6

[mikeTRON]

So why dont you split the data into multiple columns, so Colour, Cost, Names, size all have a different column of data, then you can easily make a dynamic named range on a pivot table for each?

I do not recommend having one column for multiple data dimensions in a data set.

If you had them in a different column you could make a pivot for each one that simply lists them all, and use THAT with offset and countA to get the list you are wanting.

[Pho6]

Because the data that I have actually automatically refreshes from an external source and the output is produced that way onto excel...

[mikeTRON]

fail. Will they always be in that layout? colour above cost above name above size?

[Pho6]

Yup they will always be in that order. Just need to somehow dynamically change the range if a value under it gets added.

I was thinking maybe if somehow with a VBA which can identify a break somehow between rows? It's killing me.

[mikeTRON]

Its easy, and there are plenty of ways to do it, but if you wanted to do it with VBA, I would say use the vba to standardize the data like I said above :D

To get to what you are trying to accomplish check out the following file. I used helper cells so you can walk through what I am doing, but you could house ALL of those formulas IN the formula for the named ranges if you would like. I typically like it split out, as it is easier to audit since you cant walk through the formulas in the name manager.

Let me know if you have any questions.

TestFile.xlsx

[Pho6]

Thanks - I'm going to play around with this and see if it works.

[mikeTRON]

Oh, also note the dynamic named range I made for your PivotTableData called PivotDATA. It will grow as your column A grows and as your Row 1 grows, dynamically/automatically.

Then just refresh your pivot table and the dynamic named ranges will also adjust automatically.

[Pho6]

This worked perfectly. Thanks so much!

[Pho6]

Hi MikeTron - is it possible to add another dimension to the dynamic range and make it two columns?

TestFile3.xlsx

[mikeTRON]

Yes it is possible. You need to use the column width portion of the offset function.

Also, I don't know what you are trying to do but that is a kinda clunky solution. You could just as easily have VBA come and fix a named range, then update it anytime a pivot gets refreshed in the background.

You can see the new additional 2 column width named ranges labeled VlookupShirt/Computer/Fruit in the attached file.

TestFile4 with new names ranges.xlsx

[Pho6]

thanks so much!