Create drop down list from non-fixed column height
Create drop down list from non-fixed column height
Following this:
https://www.youtube.com/watch?v=oxiSaODGsyo
I have a column of items I wan to turn into drop-down list, however, it is not fixed, i.e. items can be added to it dynamically as needed so I cant specific fixed range like $A1-$A50, it has to be basically $A1-$FirstEmptyCell
Is there a way to achieve this?
https://www.youtube.com/watch?v=oxiSaODGsyo
I have a column of items I wan to turn into drop-down list, however, it is not fixed, i.e. items can be added to it dynamically as needed so I cant specific fixed range like $A1-$A50, it has to be basically $A1-$FirstEmptyCell
Is there a way to achieve this?
OpenOffice 3 on Windows 10/7
Re: Create drop down list from non-fixed column height
Set the data validity to Allow a Cell range and set the Source to
The OFFSET function will return a cell range that starts at A1 and contains as many rows as there are non-blank cells in the range A1:A10000.
Code: Select all
OFFSET(A1;0;0;COUNTA(A1:A10000);1)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Create drop down list from non-fixed column height
Thanks. How would I reference 2nd sheet for column? I think in Excel it is *Sheet Name*. Is there a quick reference guide for buit-in vars/syntax?FJCC wrote:Set the data validity to Allow a Cell range and set the Source toThe OFFSET function will return a cell range that starts at A1 and contains as many rows as there are non-blank cells in the range A1:A10000.Code: Select all
OFFSET(A1;0;0;COUNTA(A1:A10000);1)
OpenOffice 3 on Windows 10/7
Re: Create drop down list from non-fixed column height
If the column containing the data validity list is on Sheet2, the Source formula would be
I'm not sure what you mean by "Is there a quick reference guide for buit-in vars/syntax?". Calc includes extensive Help files and its syntax is very similar to the basic Excel syntax that has been in place for decades. Is there a particular topic you need help with?
Code: Select all
OFFSET(Sheet2.A1;0;0;COUNTA(Sheet2.A1:A10000);1)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Create drop down list from non-fixed column height
Is Sheet# fixed reference to any sheet regardless how it is named? Is it possible to reference sheet by full name (regardless of language)?FJCC wrote:If the column containing the data validity list is on Sheet2, the Source formula would beCode: Select all
OFFSET(Sheet2.A1;0;0;COUNTA(Sheet2.A1:A10000);1)
OpenOffice 3 on Windows 10/7
Re: Create drop down list from non-fixed column height
Sheet2 is the actual name of the second sheet. If I change that sheet name to My Sheet Name, I can use that name in the formula. I have to enclose the name in single quotes because it contains spaces. I think having spaces in sheet names is not a good idea, but it can be done.
I suppose this will work for any language, though I am not sure how well characters beyond the standard Latin alphabet will be handled.
Code: Select all
OFFSET('My Sheet Name'.A1;0;0;COUNTA('My Sheet Name'.A1:A10000);1)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Create drop down list from non-fixed column height
Hmm, any idea why does it increase the offset values when row is copied? I.e.
ctrl+c
OFFSET('My Sheet Name'.A2;0;0;COUNTA('My Sheet Name'.A1:A10001);1)
I cant duplicate them properly with same functional
Code: Select all
OFFSET('My Sheet Name'.A1;0;0;COUNTA('My Sheet Name'.A1:A10000);1)
OFFSET('My Sheet Name'.A2;0;0;COUNTA('My Sheet Name'.A1:A10001);1)
I cant duplicate them properly with same functional
OpenOffice 3 on Windows 10/7
Re: Create drop down list from non-fixed column height
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Create drop down list from non-fixed column height
Make the first argument for OFFSET absolute. Probably you'll need the same for the first argument of COUNT.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers