Using named formula in validation

[naffats]

Hi, I'm having a problem with a complex validation, where the combined length
of the formulas got longer than 256 character, which seems to be the limit on
the Custom formula field under Validation in Excel 2003 (also when doing
conditional formatting?).

For various reasons (mostly related to users), I can't use the obvious
workrounds with a separate cell with the validation test, and I can't put any
macros into the spreadsheet.

So I thought, why not use Named formulas? Alas, this doesn't seem to work,
or at least not the way I expect.

Small example:
define the name Test and make it refer to "=$A$1>1".
set custom validation on cell $A$1 to "=Test".

Now I expect a warning on the value '0' in cell $A$1, but actually anything
goes.

Am I missing something?
Is there any other way around the size limit on validation formulas, that
does not involve macros or extra cells/sheets?

Cheers /naffats

 

[Stefi]

Try this way:
define the name Test and make it refer to "=$A$1".
set custom validation on cell $A$1 to "=Test>1".


Regards,
Stefi


„naffats†ezt írta:

 

[Joel]

I works for me in 2003. I added an equal sign in front of the named range in
the source box for the data validation and set the option in the allow box to
List

 

[naffats]

Thanks, but that doesn't solve the problem with the >256 character long
formula; now I have a range named Test, and I'll still have all of the
formula code in the validation formula box.

/naffats

 

[naffats]

Thanks, but there's no problem getting it to work when the name refers to a
range, but I need to use a name that refers to a formula. See my answer to
Stefi ...

/naffats

 

[Dave Peterson]

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]

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]

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.

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

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]

 

[naffats]

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

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.

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

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]