Shared workbooks and Arrays

[Kabeer456]

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