Shared workbook - Issues with Dynamic Ranges & Tables

[spydey]

I have a number of different workbooks, let's just call them workbook 1, workbook 2, etc....

Each workbook has multiple worksheets referencing different data from the other workbooks/worksheets.

I originally had different named ranges (static) in each worksheet, to make things easier to manage.

Using those named ranges simplified referencing closed workbooks in the various formulas each workbook/worksheet had.

I have an issue though, the range of data was growing and shrinking in each workbook as they were being used.

So I decided to make my named ranges dynamic so that the named range would grow and shrink as data was added/removed from the different worksheets.

But I ran into a problem.

If I have all workbooks closed, open one of them, for example workbook 1, I would get an error message in the Edit Links:

Error: Undefined or Non-rectangular name

I would also get this:

Microsoft cannot find 'ItemNum_Assigned' on 'Workbook2.xlsx'. There are two possible reasons:

• The name you specified may not be defined.
• The name you specified is defined as something other than a rectangular cell reference.

Check the name and try again.

I believe that the issue is stemming from my dynamic named range in the workbook I am referencing, i.e. workbook 1 is referencing workbook 2's dynamic named range (workbook 2 being closed).

Is that a correct assumption on my part?

So I figured I would convert all of them to tables.

This would facilitate the data growing and shrinking automatically, I wouldn't need named ranges, I could just reference the table column, etc.

I went through each workbook and each worksheet and converted them to tables.

It seemed to work fine!! Until I had to share the workbooks .... as in make it possible so that multiple people are in the same workbook at the same time.

I can't share them because of tables and/or XML data.

So it looks like my only option is to have my named ranges be static but have them extend to a range sufficiently large enough where they will never "overfill", meaning data doesn't ran past the end of my named range.

Does that all make sense?

Any ideas on how I can either use tables (preferred!!!) in a shared workbook or use dynamic ranges and have them function across multiple workbooks, with many of them closed?

Also, as an example, my dynamic named ranges are formulated like this:

=Sheet1!$E2:Index(Sheet1!$E:$E,CountA($A:$A))

What do you think? Is there any hope???

-Spydey