Using OFFSET function for data validation

mgottli · Newbie Joined: 17 Dec 2005 Posts: 3

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!!

Villeroy · Posted: Mon Dec 19, 2005 11:07 am Post subject:

No calculated lists for now. May be you are satisfied with a workaround:
http://www.oooforum.org/forum/viewtopic.phtml?t=28560

mgottli · Newbie Joined: 17 Dec 2005 Posts: 3

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!

Villeroy · Posted: Tue Dec 20, 2005 12:51 pm Post subject:

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.

mgottli · Newbie Joined: 17 Dec 2005 Posts: 3

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!

Villeroy · Posted: Tue Dec 20, 2005 3:02 pm Post subject:

Thank you for the details!
Oh, that's soo easy Cool

controlc · General User Joined: 13 Dec 2005 Posts: 19

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?

Villeroy · Posted: Sat Jan 28, 2006 12:57 pm Post subject:

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

controlc · General User Joined: 13 Dec 2005 Posts: 19

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

Villeroy · Posted: Sat Jan 28, 2006 4:48 pm Post subject:

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.