SUBNM & Validation Lists in XL2003

[Steve Vincent]

This is a minor annoyance to a few of our users but I can see it causing all sorts of issues when people don’t notice it. I’d like to avoid the issue in the first place but so far I’ve not had much luck.

We have a graph pack that shows data from various cubes, part of which is selectable by the user. 2 panels of options are at the top of the file, one with SUBNM formulae to things like site, scenario, and department so the user can pick the area of interest. The other is a bunch of validation lists so the user can choose what bits of the graph to show (demanded heads, actual heads, planned heads, scenario etc).

All but one of the data ranges for the validation lists are static, the one that changes is the scenario and it’s this which is causing the issue. A user gets to select 2 scenarios from 2 different cells with SUBNM in them. This is picked up by the range for the validation list by a simple =A3 type formula. If the sheet is recalculated the range updates fine but the value in the validation list does not. The user must reselect the scenario they wish plot otherwise the data does not refresh correctly.

screenshot of user options

criteria.jpg (28.83 KiB) Viewed 2624 times

Example (before a change to the selection);

Selected in the SUBNM cells
Option A – Scenario Alpha
Option B – Scenario Beta

Validation range
Option 1 – Scenario Alpha
Option 2 – Scenario Beta
Option 3 - Both

Validation data
Option 1 – Scenario Alpha
Option 2 – Scenario Beta
Option 3 - Both

Example (after a change to the selection);

Selected in the SUBNM cells
Option A – Scenario Alpha
Option B – Scenario Charlie

Validation range
Option 1 – Scenario Alpha
Option 2 – Scenario Charlie
Option 3 - Both

Validation data
Option 1 – Scenario Alpha
Option 2 – Scenario Beta
Option 3 - Both

I am trying to find a way to force the option picked in the validation data to update when the user selects something different from the SUBNM cells. In effect showing the value from option 1 or 2 rather than remembering the previously selected value.

I did find some VBA code that dealt with the issue but only when the validation range was changed manually. In my case it’s only altered by F9 so the trigger events didn’t work. If anyone has any ideas they would be most welcome, thanks.

[Wim Gielis]

Hi

In VBA, you could use the Worksheet_Calculate() event, no?

But of course, this requires to use VBA which will not work on the Web for instance.

Another event is the Worksheet_Change(ByVal Target As Range) event, based on changes in one of the 2 "SUBNM-cells".

HTH,

Wim

[Steve Rowe]

You might want to look at writing a very simple VBA funstion that reproduces whatever is in the Excel cell that doesn't work but put Application.Volatile in the function which should force the calc. If you can't use VBA then I'm not sure...
HTH