On Wednesday, 26 October 2022 at 15:10:55 UTC-6, David Cuthill wrote:
On Wednesday, 26 October 2022 at 15:00:53 UTC-6, David Cuthill wrote:
I have a named ranged that I am using to support a validation list for a cell, say cell G11. Depending on the value selected for G11 I am trying to get the validation list of cell G13 to use the same named range but to only present values from the
named range that are greater than the value selected for G11.
data_lst is the named range - from B3 to B12. What I have done is shown below but while is shows the correct range as $B$5:$B$12 (B5 being the first cell in the range greater than the value of G11) it doesn't work to be inserted in the validation
list for cell G13. I get an error of "The list source must be a delimited list or a reference to single row or columns".
Any help would be greatly appreciated. Hopefully it a simple fix.
=CONCAT(CELL("address",INDEX(data_lst,MATCH(TRUE,data_lst>G11,0))),":",ADDRESS(MAX(ROW(data_lst)),MAX(COLUMN(data_lst))))
Okay - well did a bit more checking and just by adding INDIRECT to the found of all this it now works. Maybe not the most elegant way of doing things but it seems to now work.
I'm using this formula as the source for the validation list and it works fine. But if I close the file and then later reopen it, that drop down does not work until I select the cell and then select Data Validation and then do nothing more than click
okay without making any changes. After that it works fine until I again close and reopen it. Is this a known behavior or is there something amiss with the way I have constructed the source for the validation?
=INDIRECT(CONCAT(CELL("address",INDEX(csgsize,MATCH(TRUE,csgsize>$B$18,0))),":",ADDRESS(MAX(ROW(csgsize)),MAX(COLUMN(csgsize)))))
csgsize is a named range.
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)