Very silly question about data validation

[RonOliver]

Hi! I'm using this formula:

=IF(K15=1,"1,2,3,4,5,6,7",IF(K15=2,"1,2,3,4,5,6",IF(K15=3,"1,2,3,4,5",IF(K15=4,"1,2,3,4",IF(K15=5,"1,2,3",IF(K15=6,"1,2",IF(K15=7,"1","1,2,3,4,5,6,7")))))))

... as a source for my drop-down list in data validation. The way this works is I want K14 to give me a list of available numbers depending on the value in K15. In any case, the combined number must not exceed 8. However, I'm getting a "list source must be a delimited list." I don't know what I'm doing wrong. Can you please help me with this? If there's a better formula I can use, I would also really appreciate it.

 

[Snakehips]

@RonOliver Can you use a 'helper' range, like O1:O8 in example below?
Define as named range eg LISTA, defined using

=INDIRECT("O1:O"&8-$K$15)

Column O can be hidden if you wish.

Book1
	J	K	L	M	N	O	P
1						1
2						2
3						3
4						4
5						5
6						6
7						7
8						8
9
10
11
12
13
14		4
15		3
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
K14	List	=LISTA

 

[Peter_SSs]

Another option would be to use a formula like I have in O1 then use Data Validation as shown below. As with the previous suggestion, column O could be hidden if you want.

23 04 22.xlsm
	K	L	M	N	O
1					1
2					2
3					3
4
13
14
15	5
DV

Cell Formulas
Range	Formula
O1:O3	O1	=SEQUENCE(8-K15)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
K14	List	=$O$1#

In any case, the combined number must not exceed 8.

If it is possible that K15 could itself be 8, then change the O1 formula to

=IFERROR(SEQUENCE(8-K15),"")

 

[RonOliver]

These two suggestions seem great. Is there a way not to use a helper column in this case? If not, I'll stick to that.

If it is possible that K15 could itself be 8, then change the O1 formula to

K15 cannot be 8. There's also a Data Validation list there.

 

[Snakehips]

Is there a way not to use a helper column in this case?

I don't believe there is and the fact that @Peter_SSs didn't, probably endorses that.

Given that you have 365 then perhaps proceed with Peters approach, possibly tweaked as below?

Peters current DV list will range 1 : 8 if K15 is blank.

If you need that list to be 1 : 7 when K15 is blank (and when K15 is 1) then try change the formula in O1 to

=SEQUENCE(8-MAX(K15,1))

 

[RonOliver]

I understand. I really appreciate your help. Thank you!

 

[Peter_SSs]

Is there a way not to use a helper column in this case?

There are 2 options that you could consider if you really don't want a helper column.

1. You could set up Data Validation in K14 as shown below. It will only allow entry of the values you want, but there will not be a drop-down list available to choose from.
   
   
2. If vba is allowable, then a new DV drop-down list could be set up each time cell K15 is altered. Would that be acceptable/preferable?

23 04 22.xlsm
	K
14
15	5
DV

Cells with Data Validation
Cell	Allow	Criteria
K14	Whole number	between 1 and 8-K15

The Data Validation would be set up like this

 

[RonOliver]

There are 2 options that you could consider if you really don't want a helper column.

1. You could set up Data Validation in K14 as shown below. It will only allow entry of the values you want, but there will not be a drop-down list available to choose from.
   
   
2. If vba is allowable, then a new DV drop-down list could be set up each time cell K15 is altered. Would that be acceptable/preferable?

23 04 22.xlsm
	K
14
15	5
DV

Cells with Data Validation
Cell	Allow	Criteria
K14	Whole number	between 1 and 8-K15

The Data Validation would be set up like this

View attachment 90685

That's OK. I'll use a helper column (maybe in a separate hidden sheet). I kinda need the list (the file is not for me but for others to use) and I can't use macros, as I can't save the file as a macro enabled Excel file. Thanks very much for your help. Hoping Microsoft looks into this in future Excel updates.

 

[Peter_SSs]

That's OK. I'll use a helper column ..

Cheers. Good luck with it!