Using Offset to create a validity dropdown

English
, ,
#1

I want to know if I should be able to do this as a Data Validity formula:

OFFSET(offsetFrom,C18-1,2,1,1)

I was expecting to get a one row dropdown with the value ‘Manage Events’ - which I get as a value if I use this:

OFFSET(offsetFrom,C18-1,2)

offsetFrom is a named one cell range which is a cell on another tab; I have tried changing the formula to use the cell reference but it still doesn’t behave as I expected.

This is part of a bigger scenario I was attempting but if I can’t use OFFSET in this way I need to rethink? Or is there another problem I have missed?

Thank youExampleTryingDependantDropdowns.ods

#2

Dismiss the comma as the parameter separator. You can choose the (better/original/correct) semicolon for the purpose under >Options>LibreOffice Calc>Formula>>Function>>Separators>>Function:.

#3

why is it better - just curious

#4

**This link** was already contained in my original answer (“What about this issue?”).
It may take a few minutes to understand the relevant aspects.
By the way: Don’t publish (attach) files containing external links. Break (>Edit>Links to External Files...) the links when preparing a document for upload.

#5

Yes. You should be able, and I more than once used such a construct though I don’t like the Validity feature generally.
I also don’t use named ranges often, and to use them in a question here basically makes it impossible to serach for expectable errors. Similar concerning the content of your C18 (which might be the text “7” e.g.).
OFFSET($A$1;ROW();0;1;1) works for me as expected.
What about this issue?
Formulas used for data validity or for conditional formatting suffer from the same problems, but don’t return visible errors. Always try your formulas also outside of the setting for the mentioned tools. If a formula doesn’t work in a cell it won’t work anywhere, probably.

===EDITING 2010-03-11 about 17:10 CET===
See this attachment.

#6

C18 contains the result of a Match statement and is numeric.
I use named ranges a lot because it makes the formulae more understandable - sorry
I don’t think it’s the separaters or English Names problem.
I suspect it’s something incredibly simple that I haven’t spotted.
Here is a link to a file that has the example I was playing with:

Thank you for your help

#7

What you prefer and use I don’t worry too much about. My concern is to get the information.I need to be able to give a reasonable and hopefully even useful answer. If you think “it’s the separators” also isn’t my concern. I need to know what separator actually is chosen. Nothing what I wrote was about “English Names”.
The way you give your example is useless for me. Make available a .ods showing the issue for you. I then can open it with LibO, and quite likely find the cause of the problem. You have more than 20 “karma” points. That’s by far enough to make a sensible attachment and leave that google nonsense aside…

#8

Did the link not work? I will do an attachment then

#9

See the attachment I made to my answer.
I couldn’t find any cell having a ‘Validity’ set.
The way things went then here, my suspicion is the error was actually caused by the separator, but I cannot know for sure as long as you don’t tell what separator actually was set at your place Too bad. .
You obviously didn’t accept the advice to test your ‘Validity’ range and CF formulas in ordinary cell ranges.
That was a bad idea.

#10

OK, I have found the problem thanks to your update to my file: I was using ‘Formula’ instead of cell range. So it was something simple as I suspected.

Just so you know:

There was a cell with validity set it was C21, the one in my step 4 of trying the ‘peices’ of the puzzle.

I did read your article which discusses localisation and separaters, the cases don’t apply to me and I am using commas which I have never experienced a problem with.

I did test my validty formulas in ordinary cells as you should have been able to see in the file I sent.

I come from an MS Office backgound and have moved to LO, which I like, it would be nice to feel thatI was being encouraged instead of scolded especially as you obviously have great skills and I would like to feel I can ask the dumb questions.

#11

Quoting @Lizat: “…being encouraged instead of scolded…”
Sorry! I didn’t intend something like scolding you. Your perception may have been mislead by:

  • my poor English
  • my preferene to contents as compared to manners.
    Nonetheless I want to express my respect.
    Quoting @Lizat: “…the cases don’t apply to me and I am using commas which I have never experienced a problem with.”
    Again without intending to scold: Whether you or any specific user experienced problems with the comma as the parameter separator wasn’t my concern when I posted to this thread you referred to. My concern was “The Case for Suitable Means” regarding global cooperation about the usage of Calc, and in specific the sharing of formulas. In fact my posts there intended to make that very clear. I may have failed.
    It’s global cooperation and the advancement of FLOSS I’m ready to spend a lot of time for.
#12

In case anyone else arrives here with the same problem - that is a perfectly formed OFFSET formula that should work in validity and isn’t…

Check that you are using it as a ‘cell range’ and NOT a ‘formula’ in the validity definition.

#13

Thanks for hinting me to the new (as of V 6.2) Custom>Formula mode of the Validity tool.
See also: https://bugs.documentfoundation.org/show_bug.cgi?id=96698#c12

#14

See also tdf#131309

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.