Not able to paste formula returning array in "list" of data validation in Excel

Question

Hello, this question refers back to Abhay19's question of Not able to paste formula returning array in "list" of data validation in Excel on 23may23. I have had same problems and followed thread with exactly the same issues down to SnowLu MSFT's response which works a treat expect for when you wish to have a column with drop down dependent cells. This first cells list doesn't have space to 'show' with the range/list because of the formula in the cell below and there is a SPILL error.

Any advice? As per Abhay its really frustrating seeing all the videos out there show how simple this should be but it is a not simple! Or not to me....

Thank you!

Answer 1

The problem is that you want the source for the data validation to be a sorted list. The SORT function returns an array, not a range, so you cannot use the SORT formula directly as source for the data validation list. Excel refuses to accept the formula.

Perhaps Microsoft will allow formulas that return an array to be used in a future update, but I have no idea whether that is in the works or not.

This problem does not occur if the formula only involves functions such as INDEX, OFFSET, MATCH and COUNTA. You can then use the formula in the Data Validation dialog itself.

The workaround is to place the formula in a cell, and to use the spill range of the formula as source.

That works fine for a single cell, but if you want to do this for multiple cells in a column, you cannot place the SORT formulas needed in a column: because the cells are below each other, the spill ranges of the SORT function would overlap, causing the #SPILL! error you saw.

Answer 2

Thanks Hans

It would work but I guess if I make the row wide enough/ create a new page per month to keep it more manageable. Thank you

Really dumb question though, why isn't anything else I am doing working? As per the original thread I found with the same problem I was having from Abhay_19, you can find no shortage of videos showing you how to create dependent lists in columns and they just cut an paste a working formula (and even formulae with named ranges) and off they go. No errors in data validation, no problems copying, no need for an extra colum (or row). What am I missing?

Regards, Lesley

Answer 3

As a workaround, place the formulas next to each other in a row, instead of below each other in a column.

See https://www.dropbox.com/scl/fi/8xeznh4vocfpokpfiqzkr/Forestry-timesheets.xlsx?rlkey=g795dzqdxg99gbvi58pckd8ar&dl=1

Answer 4

Hi there

Thanks so much for quick reply!

Here is a link. We are essentially wanting a group of our employees to fill in a simple timesheet. The part that is a problem is we will ask then to select a selection of woods they are working in (column E) and that would produce a subcompart list of areas within that block of woods to pick from. Nothing difficult or so I thought. I've tried using xlookup on one sheet to get a working formula, offset on another but neither seem to be able to be used in data validation. I tried as suggested to remove all range names and put the forumla to produce the range in a cell (column F) to the refer the data validation to using '=f2#'. Hence the spill now.

Forestry timesheets.xlsx

Hope this link works!

Answer 5

Could you share us a test file or some screenshot and the formula you are using?

For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.

Why a sample file is important for troubleshooting. How to do it. - Microsoft Community

You may try transpose function to see whether data will show.

=transpose(Your formula)