CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
Code Poet
Code Poet

Reputation: 11437

Google Sheets - Data validation from a horizontal range

I'm trying to do something which seems straight forward enough. In the cell (D15) validation rules, I want to choose, List from a range and specify a range that is horizontal, instead of vertical. Please see the following self-explanatory screenshot.

Horizontal Data Range

However, this doesn't work. The drop-down only shows data from the first column of the given range. See the screenshot below.

Data from first column of range

How can I make this work? I expect to see - Cat, Mouse, Lion, and Deer in the drop down list.

PS: Arranging the items I want in the list, vertically, is not an option.

SOLUTION Similar in principle to the accepted answer by @Marios, I found a solution for my exact use case here. Unfortunately, AppScript seems to be the only answer.

Upvotes: 1

Views: 1520

Answers (4)

Shiva
Shiva

Reputation: 348

If your column range is not dynamic select end range of your column along with row i.e with the given example instead of inserting range as E15:15 set range as E15:H15 that will populate all your range in dropdown

enter image description here

Upvotes: 0

TheMaster
TheMaster

Reputation: 50462

This seems to be a bug in the desktop web app:

  • Horizontal range or vertical direction of the range is not the problem
  • However, When horizontal range is used, the data validation range doesn't accept a one close ended(F3) and a one open ended(3) range (F3:3). If both ends are
    • open, 3:3: The data validation is successful
    • closed, F3:M3: The data validation is successful
    • one side open and one side closed: F3:3. This should actually calculate the lastColumn and input the range accordingly. But,The data validation input dialog drops the open end and saves the input as just F3. This results in the only one cell drop down as shown in your question.
  • If you are able to bypass the modal dialog of data validation and somehow force F3:3 into the datavalidation configuration(eg through official mobile apps or scripts), the data validation is set properly.
  • Consider sending feedback to Google along with this post using Help > Help sheets improve.

Upvotes: 3

Diego
Diego

Reputation: 9571

I understand that you want to display those values horizontally so arranging them vertically in that sheet is not an option, but can you do so elsewhere?

In situations like this, I prefer to have at least one other sheet where I can list input values–let's call that sheet "INPUTS". In each column I may manually list out the values or use some formula to manually populate it (e.g. =TRANSPOSE('Dynamic Brands'!E15:15)). Now I can use that new range in the data validation.

Alternatively, you can build the data validation with row data, but without the "Show dropdown list in cell" feature. Change your criteria to a custom formula and use this formula =ISNUMBER(MATCH('Dynamic Brands'!D15, 'Dynamic Brands'!E15:15, 0))

Upvotes: 1

Marios
Marios

Reputation: 27350

Alternative Solution:

How about the following Google Apps Script solution.

The following code will create a data validation in cell D15 and the data validation values are given by the range E15:H15:

function setDataValidation() {

const ss = SpreadsheetApp.getActive();
const sh = ss.getActiveSheet();
var cell = sh.getRange('D15'); // data validation cell
var range = sh.getRange('E15:H15'); // range of options
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
cell.setDataValidation(rule);
}

Instructions:

  1. Click on Tools => Script editor:

step1

  1. Copy and Paste the aforementioned code snippet into the script editor and click run (play button):

step2

Further ideas:

  • Instead of executing the function from the script editor, you can create a button in the google sheets UI to execute this function for you.
  • Another approach would be for the user to select the range of the list he wants as values for the data validation cell and then click the button to automatically create a data validation cell for him.
  • You can also create a trigger to create a data validation cell when something is edited.
  • Instead of creating a data validation only for one cell, you can do it for a range of cells etc.

References:

Upvotes: 1

Related Questions