Select from a List then refer to another LIST

[georgiabrown]

FOR Example

Cell A1 Has a drop down list of various types of pests in Grape Vines.
This cell may include such pests as Downy, Powdery, Botrytis, Rust Mite etc

For each of these pests there are lists of known chemicals that can be used for control

These Chemicals can be set up in a table with the PEST as headings and the control chemicals listed under each of those headings.

I would like the user of the spreadsheet to

Firstly identify the PEST. (This comes from the drop down menu in Cell A1
THEN

For the identifed PEST in Cell A1, Produce a list in CELL B1 of the possible chemicals to counteract that PEST, drawing on information from the Table

I don't know how to Program CELL B1

Any help greatly appreciated

thanks

Ross -Grape Grower and Wine Buff

[TMS]

You could use INDEX/MATCH to get the list. Use COUNTIF to control blank returns.

Regards, TMS

[twiggywales]

or you can create create a dynamic list using offset

its not to tricky once you know how. there was a thread on here ill try and dig it up

[georgiabrown]

Thanks TMS, but I don't quite understand how to achieve this. Cell B1 needs to provide the LIST of chemicals that correspond to the selected PEST. One chemical is then chosen from the drop down list, which then provides other data base information for the user. This latter part is working okay. I see another response and will check that first before responding further.
thanks

[twiggywales]

if you upload a book and show what pests you need and what chemicals (or a few) that will do it im pretty sure i could know you up something

[georgiabrown]

or you can create create a dynamic list using offset

its not to tricky once you know how. there was a thread on here ill try and dig it up

I have uploaded a section of the spreadsheet. This shows how the programme currently works BY usings KNOWN PESTS. Below section shows how I would like the user to identify the problem from a drop down list and then Select a Chemical from another LIST.

Does this make sense??

[twiggywales]

i cant see the attachment

[georgiabrown]

I am new to the Forum and did not complete the upload.

Hopefully it is loaded now

[twiggywales]

sorry somthing has come up and im a bit busy im not gonan be able to spent to amount of time required to do this especailly as you have merged boxes makeing stuff much more annoying. (dont merge lol)

ill try again later but at the moment im really busy

[georgiabrown]

okay thanks. Look forward to any help later

[twiggywales]

here you go
CHEMICAL DATA BASE EXTRACTION altered.xlsx

here are the steps incase you need to replicate (this was made more akward with your merged cells )

1)i created a spare sheet (so as not to move anything you might need)

2)created an anchorcell (makes the formulas easier to deal with)

3)1st dynamic list is a named range called pest with this formula

Please Login or Register  to view this content.

this will expand and contract if you alter your pests

4) uesd this list for data validation in main sheet cells B29,B31,B33 (GUUUUUUURRRRR MERGED CELLS )

5) used this formula for data validation in cell D29 (alter to suit)

Please Login or Register  to view this content.

this second array expands and contracts with your list of chemicals (it does this by matching the row and then count the non blank cells and using that as the hight offset in the first offset formula)

6) repeated for the other cells ( Merged cells make me sad)

hope this helps

regards

Twiggy

[georgiabrown]

Fantastic. You're a genius.

If you ever get to Australia, look me up for a bottle or two of fine Yarra Valley wine

regards

Ross Baldwin

[twiggywales]

sounds great lol

remember to mark it solved and give a little star tap if i helped you out