OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Using OFFSET function for data validation

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
mgottli
Newbie
Newbie


Joined: 17 Dec 2005
Posts: 3

PostPosted: Sat Dec 17, 2005 1:57 pm    Post subject: Using OFFSET function for data validation Reply with quote

I tried setting the validity for a cell (Data>Validity) to a cell range. I specified a formula using the OFFSET function in the cell range field. The OFFSET formula was correct, but it did not work properly in the Data Validity field. Is this a limiitation of OpenOffice Calc? The exact same formula worked fine in Excel (with ; replaced with , of course).

Thank you for your help!!
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Dec 19, 2005 11:07 am    Post subject: Reply with quote

No calculated lists for now. May be you are satisfied with a workaround:
http://www.oooforum.org/forum/viewtopic.phtml?t=28560
Back to top
View user's profile Send private message
mgottli
Newbie
Newbie


Joined: 17 Dec 2005
Posts: 3

PostPosted: Tue Dec 20, 2005 12:38 pm    Post subject: dynamic list for dropdown validation Reply with quote

Actually, I'm not using the validation to make sure it's right as I am using it for the dropdown functionality. So unfortunately, the work around you propose will not work because it checks the validation, but does not provide a dropdown. Any other ideas?

Thank you!
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Dec 20, 2005 12:51 pm    Post subject: Reply with quote

http://www.oooforum.org/forum/viewtopic.phtml?p=114767#114767
I wrote a macro, which can be used with a listbox.
But after doing that, I found a way how to use dynamic lists without a macro.
Quote:

Well, you can not calculate the position of a validation-list by some formula.
But you can calculate the content of a validation-list!
Define some named ranges:
"Items" refers to A1:A100 and has "Item1","Item2",..."Item100"
"Weekdays" refers to B1:B7 and has "Monday","Tuesday",...
"Validation" refers to C1:C100 and has an array formula:
{=CHOOSE($D$1;Items;Weekdays)}
D1 is 1
Set validation of some unused range to range "Validation".
Change D1 to value 2.
Back to top
View user's profile Send private message
mgottli
Newbie
Newbie


Joined: 17 Dec 2005
Posts: 3

PostPosted: Tue Dec 20, 2005 1:44 pm    Post subject: Dynamic validation list ... almost Reply with quote

Villeroy,

I didn't understand everything in the macro so maybe your macro already does this. But looking at the formulas at the bottom, that won't work in this situation. The validation I am using depends on the cell next to so the validation list changes for each row. The formula solution you explain, as I understand it, requires setting up a range for each possible data entry field. I may have hundreds of data entry fields so this won't work.

The data setup on Calculations looks something like this

CategoryColumn NameColumn
Cat1 Name1
Cat1 Name2
Cat1 Name3
Cat2 Name4
Cat2 Name5
Cat2 Name6
Cat3 Name7

Then on the main sheet, the user picks a category in column A and then the data validation provides a dynamic list of possible choices for column B

In cell A3 the user picks a Category
The validtion for B3 looks like:

=OFFSET(INDIRECT(ADDRESS(MATCH(A3,CategoryColumn,0)+1,NameColumn,,TRUE,"Calculations")),0,0,COUNTIF(CategoryColumn,A3),1)

What the above function does is create a list with an offset starting at the first match for the category specified in A3 where the height of the list of equal to the number of rows that contain a match to the category specified in A3.

This data validation is repeated for B4 referencing A4, B5 referencing A5, and so on.

I appreciate your help in figuring out how to do this in OOo! I would love to not have to use Excel for this. Thank you!
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Dec 20, 2005 3:02 pm    Post subject: Reply with quote

Thank you for the details!
Oh, that's soo easy Cool
Quote:
=OFFSET(INDIRECT(ADDRESS(MATCH(A3,CategoryColumn,0)+1,NameColumn,,TRUE,"Calculations")),0,0,COUNTIF(CategoryColumn,A3),1)

Translated with semicolons and missing COLUMN()-function:
=OFFSET(INDIRECT(ADDRESS(MATCH($A$3;CategoryColumn;0)+1;COLUMN(NameColumn);1;"Calculations"));0;0;COUNTIF(CategoryColumn;$A$3);1)
Now take some free column and select a range as tall as the maximum of names you expect for a single category.
Press F2 for edit-mode, paste the formula into the active cell and press Ctrl+Shift+Enter in order to assign an array-function to the selected range.
Assign a name to the new range, if you like and assign the new range as validation-list for your input-range.
There is a cosmetic problem with appending #NA values. May be it can be solved, but I'm not shure because resizing the list leads to the original issue of calculated sizes/positions of validation-lists.
BTW: =OFFSET(NameColumn;MATCH($A$3;CategoryColumn;0)-1;0;COUNTIF(CategoryColumn;$A$3);1)
Works as well.
EDIT @22/12/2005
The "cosmetic problem" can be diminished by using another column.
=IF(ISNA(ref2OffsetFormula);"";ref2OffsetFormula)
Then set option "Sort ascending" in validation dialog.
You still have a single empty entry in the list which is less annoying.

EDIT Oct 2007:
Example file: http://www.mediafire.com/?owjae9s9uve

EDIT Sep 2009: The whole thing has been improved in v2.3
Example file with a validation formula that calculates the position of the validation list: dynValidation2.3.ods


Last edited by Villeroy on Fri Sep 11, 2009 2:13 am; edited 3 times in total
Back to top
View user's profile Send private message
controlc
General User
General User


Joined: 13 Dec 2005
Posts: 19

PostPosted: Fri Jan 27, 2006 2:46 pm    Post subject: Reply with quote

Villeroy and MGotti

Thanks alot for your help on this. I've got an example file working, but it seems impractical for switching validation lists for a spreadsheet with many rows. You can download it from:

http://homepage.mac.com/ericrjohnson/iDiskFiles/

I've not yet tried the macro:

http://www.oooforum.org/forum/viewtopic.phtml?t=29079

Or the solution suggested here:

http://www.oooforum.org/forum/viewtopic.phtml?t=28777&highlight=validation

Any advice? Should I just punt and use the macro? Can you take a look at the example spreadsheet and give me some sage advice?
Back to top
View user's profile Send private message AIM Address
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Jan 28, 2006 12:57 pm    Post subject: Reply with quote

Well, you need a database for doing that - normally.
Since this is one of those "Excel-can-do-that" kind of problem, I took some minutes and simplified your sheet "CountriesAndCities"
ColA:
USA
Germany
France
Italy
other columns have the associated cities.

Named range "Countries" =$CountriesAndCities.$A$1:$A$100
Validation-list for $ExUI.D2:D1000 = Countries

Named range "lookupCities" =$CountriesAndCities.$B$1:$IV$100
Enough space for adding 100 countries, each with a max of 255 cities.

Insert a third sheet "Validation"
Select single cell validation.A1
Ctrl+F3
Define Name "selectCountry" = $ExUI.$D2
[NOT $D$2]
From the viewpoint of $Validation.$A$1 "selectCountry" refers to the first cell of your input column for country-names.
From the viewpoint of $Validation.$A$2 "selectCountry" refers to the 2nd cell of your input column for country-names.

Select $Validation.$A$1:$IU$1 (one row 255 cols)
Array-Formula:
{=OFFSET(lookupCities;MATCH(selectCountry;Countries;0)-1;0;1;255)}
[return 1 row, 255 cols from "lookupCities" with a row offset of Match(selectCountries,Countries) -1, where row-offset is zero if first row matches]
Ctrl+C (copy array)
Select A2:IU1000
Ctrl+V (paste the single row array to 999 other rows, drag down won't do that)
Wait some seconds

Select single cell $ExUI.$D$2
Define name "validate" = $Validation.$A1:$IU1
[again with relative row-reference]
Validation of $ExUI.E2:E1000 =validate

Have fun
Back to top
View user's profile Send private message
controlc
General User
General User


Joined: 13 Dec 2005
Posts: 19

PostPosted: Sat Jan 28, 2006 2:48 pm    Post subject: Reply with quote

Villeroy -

Thank you so much for your help. That worked perfectly!

For those that may read this later, I've posted a working example at the iDisk URL in my previous post.

EJ
Back to top
View user's profile Send private message AIM Address
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Jan 28, 2006 4:48 pm    Post subject: Reply with quote

Very Happy Very Happy Very Happy
Finally we made it.
Calc's toolkit is limited, compared to the world market leader. But when you listen to it's clockwork ticking, then you may overcome most limitations.
Implementing the dynamic validation makes your workbook quite a large monster.
Restriction of maximum count of cities per country to ~20 makes it much smaller. From time to time you may lock the cells of old input-records and remove the corresponding row arrays from validation-sheet.
Another limitation: *All* dynamic contents, conditional formattings, validation lists as well as normal sheet formulas, do not work when automatic calculation is turned off.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group