CODEWITHSUNDEEP
excelexcel-formula
Cauthon
Cauthon

Reputation: 520

Create Data Validation list with cells that meet certain conditions

This is a problem that I know can be coded easily in VBA, but I'm trying to implement it in a formula.

I'm trying to build a list (using Data Validation), but I want this list to only take part of the range, which fits a certain condition.

So let's say I want the list to be stored in C5, and let's say I have a range in another sheet called Example_Range. I'd like the list to consist of cells from the Example_Range, such that the two cells above them are identical to two other cells that are above C5.

So in short, I need to only take cells from Range_Example that are below cells that are identical to cells that are above C5 (where I want the list to go). Notice that the Example_Range is in a different sheet than the sheet that contains C5.

I know that I can insert formulas into the Source field in Data Validation, but I do not know how to build this type of formulas. Any help would be appreciated!

Here's a practical example:

I have this data in "Sheet2":

enter image description here

And I want to create a list, using Data Validation, in the red cell in "Sheet1":

enter image description here

I need the list to contain 20,15,10, because in "Sheet1", above the red square, that's the input I received. If however, the two cells above the red one had "Mazda" and "Lantis", I would want the list to be populated only with 45.

So my range is the whole 4th row in "Sheet2", but I do not want all the items from that range, only those who have cells above it that are identical to the cells above the red cell (where the list will appear) in "Sheet1".

Upvotes: 2

Views: 348

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

It's a bit convoluted but I found a process to accomplish this:

In sheet 2

  1. cell A6: =IF(AND(A2=Sheet1!$A$2,A3=Sheet1!$A$3),A4,"x"), drag over to column E
  2. cell A7: =IF(A6="x","-",1)
  3. cell B7: =IF(B6="x","-",MAX($A7:A7)+1), drag over to E7
  4. cell G7: =IFERROR(INDEX($A$6:$E$6,MATCH(G6,$A$7:$E$7,0)),"X"), drag over to K7
  5. create a Workbook scope defined name range as list that refers to: =OFFSET(Sheet3!$G$7,0,0,1,COUNT(Sheet3!$G$7:$K$7))

Then use list as the data validation in sheet 1 cell A4.

Screenshot for full picture view (example is Mazda Lantis, but I tested for others as well.):

enter image description here

Upvotes: 1

Related Questions