Thanks. Trust me, the sheet and the workbook are both locked down, and we are
doing a lot of validation after reading the data, too. And we've seen all
sorts of strange input ...

Cheers /staffan

"Dave Peterson" wrote:

It sounds like putting the long formula in a cell on separate hidden worksheet
is within the bounds of your rules.

Make sure you protect the workbook's structure so that sheets can't be
moved/added/deleted/shown/hidden, too. (Be aware that this kind of protection
is easily broken.)

And be aware that data|validation isn't very robust. Copy and paste a different
cell onto that cell with data|validation and you'll see.




naffats wrote:

Well, that is the way I've ended up doing it -- putting the formulas in a
hidden cell in the same sheet, but outside the reach of the users. Still need
to get this approved by The Mgmt, though -- and that is not a certain thing,
by a long shot.

We can't use macros and would rather not use the hidden cells either because
this spreadsheet, after having data filled in by #¤%¤ users (who don't have
the knowledge to understand what they are doing with Excel, and are working
in an environment where they are not allowed to use a macros-enabled
spreadsheet), is going to be read by a Java application that loads the data
into a fairly critical database.

So we need to make sure the users can't actually screw up the structure of
the spreadsheet, nor enter incorrect data. At the same time we need
to (politely, mind you :-) tell them what is not good with their data.

Cheers /naffats

"Dave Peterson" wrote:

So why can't you put that long formula in a cell on a separate sheet (and hide
that sheet from the user). Then use that cell in the data|validation rule?

naffats wrote:

[removed]

--

Dave Peterson


--

Dave Peterson