Shared workbooks and Arrays

Kabeer456

New Member
Joined
Aug 20, 2012
Messages
10
Good afternoon all-

I've been scouring the board for a solution to no avail. You all have been extremely helpful in the past so I figured I'd post a new thread with as much details as possible. The consensus seems to be that arrays cannot be shared...so I'm looking for a workaround and some insight.

Problem:
  • Shared workbook ("Rush Tracker") contains 2 sheets (1 hidden, 1 visible)
  • Sheet 2 (hidden) contains named lists for drop down menus
  • Sheet 1 E8 references DueDates in a drop down list from Sheet 2
  • DueDatesis a range of arrays with the following code:
    • {=IF(ISNA(INDEX(DueDateList,MATCH(0,COUNTIF($A$2:A2,DueDateList),0))),"",INDEX(DueDateList,MATCH(0,COUNTIF($A$2:A2,DueDateList),0)))}
    • DueDateList is an equal sized range on Sheet 1 populated by the user (currently 200 lines to keep lag time to less than 5 seconds)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Sheet 1 Column F (formatted date entered by user) - DueDateList[/TD]
[/TR]
[TR]
[TD="align: center"]04/28/2014[/TD]
[/TR]
[TR]
[TD="align: center"]05/03/2014[/TD]
[/TR]
[TR]
[TD="align: center"]05/01/2014[/TD]
[/TR]
[TR]
[TD="align: center"]05/03/2014[/TD]
[/TR]
</tbody>[/TABLE]









[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Sheet 2 Column A (formatted date with array formula) - DueDates
[/TD]
[/TR]
[TR]
[TD="align: center"]04/28/2014[/TD]
[/TR]
[TR]
[TD="align: center"]05/03/2014[/TD]
[/TR]
[TR]
[TD="align: center"]05/01/2014[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]




Only unique entries are added to the list (NOT CURRENTLY SORTED)





  • When the workbook is shared, the list in Sheet 1 E8 produces this error message:
    • "Cannot copy or move array entered formulas or tables in a shared workbook."


Questions:

  1. Is therea way to keep a dynamic list of unique dates such that I can share a workbook?
  2. Is there a way to keep that list sorted sequentially so the drop down list populates in a friendly manner?


I'm working with Excel 2003 and have most add-ins active as I use several of the Analysis ToolPak features.

Any insight would greatly be appreciated.

Thank you.

-Brian
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Forum statistics

Threads
1,226,730
Messages
6,192,705
Members
453,748
Latest member
akhtarf3

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
Back
Top