|i want to replace the data validation list....|
thats why i wasn't telling you much about it....
This is what happens at the moment:
1)the user pastes the 'user list' into the spreadsheet
2)using dropdown menu1 (data validation:list) selects 'Type' from:
3)using dropdown menu2 (data validation:indirect list) selects 'SubType' from (eg resistors):
Resistor, Film, Insulated
Resistor, Film, RN
Resistor, Film, Chip
Resistor, Film, Power
Resistor, Film, network
Resistor, Wirewound, Accurate
Resistor, Wirewound, Power
Resistor, Wirewound, Power, Chassis Mounted
Resistor, Wirewound, Variable
Resistor, Wirewound, Variable, Precision
Resistor, Wirewound, Variable, Semiprecision
Resistor, Wirewound, Variable, Power
Resistor, Nonwirewound, Variable
Resistor, Composition, Variable
Resistor, Nonwirewound, Variable, Precision
Resistor, Film, Variable
4)The spreadsheet then uses the sub type to search a second sheet and retrieve the price of that component.
I would like to automate this.....
This being the manual dropdowns. If there was a way of searching a cell in the 'user list' for key data/strings then using the found words to form a new string, it would be really useful. eg:
I)Search in cell A45 (who's value = CAP 5.6pF 0,25P 50V CER 0603 COND.CHIP 0603 5,6pF 0,25PF 50V NP0) for the terms:
II) then when 'cap' returns true, search for :
III) then when 'cer' returns true:
IV) then when that retuns true, output 'Capacitors, Ceramic Chip'
V) this then gets put through the existing VLOOKUP search