Named Ranges and Shared Workbooks

[Ellasheba]

Hello

I had successfully created a 'dashboard' using a datasheet formatted as a table, but when sharing the workbook in the review tab, it says that sharing does not support a table and to convert to named ranges.

I have now converted the table named ranges (which allows the sharing of the workbook), but these named ranges do not appear to automatically update within functions as my original table data did....

The pivots and charts also are not supported when sharing a workbook.

Question 1: How can I set my Named Ranges to update as the worksheet is added to?

Question 2: If a workbook is shared and then unshared and saved, will it be able to update the existing pivot and charts within the contained dashboard?

Many thanks !

 

[Chihiro]

Use dynamic formula named range, rather than static cell reference in named range.
Create a dynamic defined range in a worksheet - Office | Microsoft Docs

Above uses Offset() but you can also use INDEX to create dynamic named range.
Excel formula: Dynamic named range with INDEX | Exceljet

I don't like Shared Workbooks and never use them... so can't really comment on Q2.

 

[Ellasheba]

Thanks you so much - I will have a look at the links

 

[Ellasheba]

I can’t seem to make these do what I need them to do - I know it’s me!!
I have attached a sheet and wonder if someone can show me what I’m doing wrong... I need a range that changes as rows are added (index or offset) and a return count of unique numbers in the column.
I did this when I had a table set up but now I want to use names ranges I can’t make it work

 

[p45cal]

This question (msg#4) has been reposted here:

Dynamic Named Ranges

Hi - I posted yesterday regarding named ranges and was kindly informed of index or offset to make a named range dynamic. However I cant seem to grasp how to make these work within other functions. I have attached a sheet and am trying to create a dynamic range (that will adjust when rows are...

chandoo.org