Data Validation List with OFFSET wont accept text

[DSCfromCFA]

I am using the following formula to alter the length of a Data Validation List:
=OFFSET($N$15,1,0,OFFSET($N$15,M16,0))
The formula only works if the cell in the list that corresponds with the number held in M16 is itself a number. However, the purpose of the list is to select text. All other cells can contain text, and as long as the cell that corresponds with M16 is a number, the validation works, otherwise no selection is possible and even trying to accept the Validation formula results in an message "The Source currently evaluates to an error. Do you want to continue?"


The list is a maximum of 6 rows starting at N16 and the number of rows to make the list is held in M16.

Excel version Microsoft 365 MSO (Version 2412 Build 16.0.18324.20092) 64-bit

What am I missing?
Shane

 

[coulombevin]

Hi DSCfromCFA,

Based on a test I've done with 1 to 6 in N16, your formula seems to work. As soon as a changed one value, the offset formula returned a bunch of 0. If you could provide a sample of your real data, it would help to provide the right answer.

Here's I would change the formula: *I am using the French version of Excel 365, so there might be some errors due to manual translation or discrepancies in the Excel user manual*

=CHOOSEROWS(N16:N21,SEQUENCE(M16))

Classeur1 (version 1).xlsb
	M	N	O	P
15	Number of value	list	NewFormula	OffsetFormula
16	5	1	1	0
17		3	3	0
18		5	5	0
19		7	7	0
20		9	9	0
21		11		0
22				0
23				0
24				0
Feuil1

Cell Formulas
Range	Formula
O16:O20	O16	=CHOOSEROWS(N16:N21,SEQUENCE(M16))
P16:P24	P16	=OFFSET(M16,1,0,OFFSET($N$15,M16,0))
Dynamic array formulas.

Bests regards,

Vincent

 

[StephenCrump]

I am using the following formula to alter the length of a Data Validation List:
=OFFSET($N$15,1,0,OFFSET($N$15,M16,0))

Try: =OFFSET(N16,,,M16)

Or another alternative: =TAKE(N16:N21,M16)

 

[DSCfromCFA]

Thank you both for the responses, and without trying them all, the first option suggested by Stephen worked a treat.

Regards

shane
(So much to learn - so little time)