Data Validation List to skip every other column

[ECEE3]

I can't seem to get a good formula to return a drop down list using Data Validation for every other cell in a row. I need a dropdown list that produces the values from D9:AR9, the list would only include D9,F9,H9,L9...AN9,AP9 and AR9. I can produce a formula that I can drag across columns to produce the result- OFFSET($B$9,0,COLUMNS($B:B)*2). But I'm not sure how to encapsulate that as the source for the range in Data Validation.

thanks

 

[Aladin Akyurek]

Let Sheet1, D9:AR9, house the source data.

D10, control+shift+enter, not just enter, copy across as far as needed:

=IFERROR(INDEX($D$9:$AR$9,SMALL(IF(MOD(COLUMN($D$9:$AR$9)-COLUMN($D$9),2)=0,
  COLUMN($D$9:$AR$9)-COLUMN($D$9)+1),COLUMNS($D$10:D$10))),"")

Now activate Formulas | Name Manager and define DVList as referring to:

=Sheet1!$D$10:INDEX(Sheet1!$10:$10,MATCH("*",Sheet1!$10:$10,-1))

Once done, you can use DVList as Source in any cell you want a dropdown list.

 

[ECEE3]

I was trying to get away without having to create any helper tables. I am guessing what I am trying to do with formulas may be unrealistic. May have to switch over to VBA.

Thanks for the reply though.

 

[Aladin Akyurek]

I was trying to get away without having to create any helper tables. I am guessing what I am trying to do with formulas may be unrealistic. May have to switch over to VBA.

Thanks for the reply though.

Yes, if it bothers you to have a result table directly in a range of cells.

 

[ECEE3]

I am adding these changes to a completed sheet after the fact and I didn't have much room to place any other result data. I can hide it outside of the printable range and make it work.


I was just trying to see if there was a formula to gather the needed data directly from the existing range of cells rather than creating a new range and gathering the data from there.


Your solution worked great, thanks for the help.

 

[Aladin Akyurek]

I am adding these changes to a completed sheet after the fact and I didn't have much room to place any other result data. I can hide it outside of the printable range and make it work.


I was just trying to see if there was a formula to gather the needed data directly from the existing range of cells rather than creating a new range and gathering the data from there.


Your solution worked great, thanks for the help.

Not fond of hiding, I'd advise to create a new sheet called, Admin, and create that results table there.

Let Sheet1, D9:AR9, house the original data to process.

In Admin, A2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet1!$D$9:$AR$9,
  SMALL(IF(MOD(COLUMN(Sheet1!$D$9:$AR$9)-COLUMN(Sheet1!$D$9),2)=0,
  COLUMN(Sheet1!$D$9:$AR$9)-COLUMN(Sheet1!$D$9)+1),ROWS($A$2:A2))),"")

The definition of DVList now becomes:

=Admin!$A$2:INDEX(Admin!$A:$A,MATCH("*",Admin!$A:$A,-1))