I have a few, non-adjacent cells on one sheet which I want to use as a Source for Data Validation in a cell on another sheet. However, it seems Data Validation doesn't natively permit this.
So, I tried to do a work-around that involved putting formulas into some adjacent cells on another sheet which would reference the values I wanted from the first sheet. Then, I named the second range and referenced that name in the Data Validation Source field.
Now I have a problem where, if one of the cells from the first sheet is blank, a cell on the second sheet that references a blank on the first will show
0 instead of being blank. I tried to work around this by doing something like
=IF(Sheet1!A1="","",Sheet1!A1). However, this doesn't actually make the formula result the same as a blank cell.
This all mostly becomes problematic when I want my Data Validation to include an in-cell drop-down. My choices here appear to be either to have
0 or a blank line in the drop-down wherever there are blanks in the Source data. If the blanks were true blanks, this wouldn't happen.
So, is there any way to work around this?
A17= (Cell is blank)
A21= (Cell is blank)
=IF(Sheet1!A21="","",Sheet1!A21) (appears blank)
Sheet2!A1:A6 is named
Sheet3!A1, Data Validation is applied with source
=Validation and an in-cell drop-down. The in-cell drop-down shows:
(Blank cell included)
Under these conditions, I'm looking for a configuration that will result in a drop-down in
Sheet3!A1 that only shows
Sheet2!A1:A4 while also keeping
Sheet2!A5:A6 available should they ever be populated. Alternately, the drop-down in
Sheet3!A1 should show only
Sheet1!A1,Sheet1!A5,Sheet1!A9,Sheet1!A13 while also keeping
Sheet1!A17,Sheet1!A21 available should they ever be populated.
It seems I need a way to either:
- Directly address the non-adjacent cells in
Sheet1, in my Data Validation Source
- Get the cells in the
Sheet2to actually return blank cells when their targets in