Shared workbook - Issues with Dynamic Ranges & Tables

spydey

Active Member
Joined
Sep 19, 2017
Messages
313
Office Version
  1. 2013
Platform
  1. Windows
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??? :eek::eek:

-Spydey
 
Last edited:

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Watch MrExcel Video

Forum statistics

Threads
1,127,556
Messages
5,625,495
Members
416,112
Latest member
somenka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top