Create drop down list from non-fixed column height

[Digika]

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?

[FJCC]

Set the data validity to Allow a Cell range and set the Source to

Code: Select all

OFFSET(A1;0;0;COUNTA(A1:A10000);1)

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.

[Digika]

Set the data validity to Allow a Cell range and set the Source to

Code: Select all

OFFSET(A1;0;0;COUNTA(A1:A10000);1)

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.

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]

If the column containing the data validity list is on Sheet2, the Source formula would be

Code: Select all

OFFSET(Sheet2.A1;0;0;COUNTA(Sheet2.A1:A10000);1)

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?

[Digika]

If the column containing the data validity list is on Sheet2, the Source formula would be

Code: Select all

OFFSET(Sheet2.A1;0;0;COUNTA(Sheet2.A1:A10000);1)

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]

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.

Code: Select all

OFFSET('My Sheet Name'.A1;0;0;COUNTA('My Sheet Name'.A1:A10000);1)

I suppose this will work for any language, though I am not sure how well characters beyond the standard Latin alphabet will be handled.

[Digika]

Hmm, any idea why does it increase the offset values when row is copied? I.e.

Code: Select all

OFFSET('My Sheet Name'.A1;0;0;COUNTA('My Sheet Name'.A1:A10000);1)

ctrl+c
OFFSET('My Sheet Name'.A2;0;0;COUNTA('My Sheet Name'.A1:A10001);1)
I cant duplicate them properly with same functional

[robleyd]

[Tutorial] Absolute, relative and mixed references

[robleyd]

Make the first argument for OFFSET absolute. Probably you'll need the same for the first argument of COUNT.