CODEWITHSUNDEEP
excelexcel-formula
AxleWack
AxleWack

Reputation: 1911

Create Data Validation(DropDown) based on certain criteria

All I want to do is display data in a data validation(DropDown) based on certain criteria. Is it really that difficult to display A8:A13 where E8:E13=Vehicle ??? I know im very new to excel, but this just seems like im being stupid!

I have this formula(Which is a name range), it displays the correct data, but ONLY based on the value from another cell:

=OFFSET('Fleet List'!$A$9,MATCH($A$8,'Fleet List'!$A$9:$A$13,0)-1,1,COUNTIF('Fleet List'!$E$9:$E$13,"Vehicle"),1)

Ive tried playing with INDEX and OFFSET, but not coming right, possibly because I dont understand how they fit together :( Im feeling real dumb today and about to start punching my screen...

This is how my table looks:

|Fleet No.|Reg No.|Vehicle Type|

    A1       ABC1     Vehicle
    A2       ABC2     Vehicle
    B1       XYZ1     Trailer

Now all I want to do is display a list for Fleet No. from Sheet2 Where Vehicle Type = Vehicle, in Cell A1 in Sheet1.

It cant be that complicated in Excel can it ?

You may laugh at me, but I just really need some help. Thanks in advance!

Upvotes: 0

Views: 97

Answers (1)

AxleWack
AxleWack

Reputation: 1911

I found a solution, just making use of a formula, which is what I was wanting to do:

=OFFSET(FleetNoRange[Fleet No.],MATCH("Vehicle",'Fleet List'!$E$9:$E$13,0)-1,0,COUNTIF('Fleet List'!$E$9:$E$13,"Vehicle"),1)

Im still sure there is an easier way - But this works, and I dont have time to keep looking, otherwise I might be minus a screen or laptop soon.

Thanks for the replies nevertheless guys.

Cheers!

Upvotes: 0

Related Questions