Differences in results using OFFSET between Excel 2010 and 2007

[rznfcc]

I created a data validation formula for an Excel 2010 workbook that would only display client names who were assigned to the manager that they selected. The formula works fine in Excel 2010. However, when I open the same workbook in Excel 2007, the data validation type is reset to "Any value" and the formula is missing.

OFFSET is an available command in both 2007 and 2010. When I try to add the formula as a data validation list in 2007, an error indicates that Offset cannot access data in a different table. I have not been able to find any documentation which restricts the OFFSET command in Excel 2007 but there seems to be some incompatibility between the two Excel versions.

The formula I used was:

=OFFSET(Clients!$A$1,MATCH(Summary!$A$1,Clients!$A:$A,0)-1,1,COUNTIF(Clients!$A:$A,Summary!$A$1),1)

Where:

• "Clients" is a sheet which lists all clients under each manager.
• "Summary" is a separate sheet which contains the manager's name. Only clients matching this name will be available in the employee's client list.
• There are 50 "employee" sheets, each which should use this formula for data validation in the client column.

The error we received seemed to indicate that the above command could not access data on another worksheet. It is not an option for me to copy the client list onto each sheet that uses this formula. Are there any other options?

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Beginning with xl2010, Excel can reference values in other worksheets of the same workbook for data validation rules.
Prior to that directly referencing ranges on another sheet was not allowed for data validation or conditional formatting.

I no longer have xl2007, but I'm pretty sure you can use a Named Range (with workbook scope) to reference another sheet in the same workbook in your DV rule.