Drop-Down Menu Works in OpenOffice, Wrecked in Excel

[Erik the Awful]

I built a spreadsheet to sanity-check engine builds. It works fabulously in OpenOffice. I saved it as an .xlsx file and tried opening it in Excel, and it's junk. The minor, surmounted issue is that Excel threw in a bunch of "@" symbols in the formulas. A quick Ctrl+H to replace the "@" with "" fixed that. The drop-down menu issue is one I don't know how to fix.

The "Engine Build" sheet has drop down menus under "Description" for swapping parts. Column A is static. Column B is the drop-down menu. Column C uses index-match formulas to pull data from the "Parts Data" sheet to check that part for specification inconsistencies between parts. Columns D, E, and G also use index-match to pull data from "Parts Data". F and H are used to calculate cost.

Part​	Description​	Mis-match?​	P/N​	Source​	Qty​	Price Each​	Total Price​
Engine Block​	Reuse Stock 327/350 Block, 4.000” Bore​	​	-​	-​	​	$200.00​	$200.00​
Crankshaft​	Reuse Stock 305/350 Crank, 3.480” Stroke​	​	-​	-​	​	$0.00​	$0.00​
Main Bearings​	Mahle P Series Tri-Metal​	​	MS909P​	RockAuto​	​	$31.79​	$31.79​
Main Bolts​	Reuse Stock Bolts​	​	-​	-​	​	$0.00​	$0.00​
Connecting Rods​	Speedway Motors Small Block Chevy 5140 Steel I-Beam Rods, 5.700"​	​	91015394​	Speedway Motors​	​	$278.99​	$278.99​
Rod Bolts​	Reuse Stock Rod Bolts​	​	-​	-​	​	$0.00​	$0.00​
Rod Bearings​	Mahle P Series Tri-Metal​	​	CB663P8​	RockAuto​	​	$28.79​	$28.79​
Pistons​	Speed-Pro Hypereutectic Pistons, 4.000”x3.480”, 5.700”, +6.9 cc​	​	H345DCP​	Summit Racing​	​	$174.99​	$174.99​
Piston Rings​	Sealed Power Piston Ring Set, 4.000"​	​	E-251X​	RockAuto​	​	$36.79​	$36.79​

My parts data is stored on a separate sheet. Each column is stored as a Named Range. Column A is stored as PartType, Column B is PartDescription, etc.

Part Type​	Part Description​	Part Number​	Part Source​	Price Each​	Bore​	Stroke​
Engine Block​	Reuse Stock 305 Block, 3.736” Bore​	-​	-​	$100.00​	3.736​	​
Engine Block​	Reuse Stock 327/350 Block, 4.000” Bore​	-​	-​	$200.00​	4.000​	​
Engine Block​	Reuse Stock 400 Block, 4.125” Bore​	-​	-​	$500.00​	4.125​	​
​	​	​	​	​	​	​
Crankshaft​	Reuse Stock 327 Crank, 3.267” Stroke​	-​	-​	$0.00​	​	3.250​
Crankshaft​	Reuse Stock 305/350 Crank, 3.480” Stroke​	-​	-​	$0.00​	​	3.480​
Crankshaft​	Reuse Stock 400 Crank, 3.750" Stroke​	012FAE362​	Summit Racing​	$0.00​	​	3.750​
Crankshaft​	Summit Racing™ Cast Crankshafts, 3.750” Stroke​	SUM-180368​	Summit Racing​	$183.99​	​	3.750​
​	​	​	​	​	​	​
Main Bearings​	Reuse Stock Bearings​	-​	-​	$0.00​	​	​
Main Bearings​	Mahle P Series Tri-Metal​	MS909P​	RockAuto​	$31.79​	​	​

In OpenOffice, the "Source" formula used in the Data Validation for the drop-down menus is "IF(PartType=A4,PartDescription)" (where A4 is the Engine Block, etc.) and it works perfectly. As an example, on the Engine Build sheet I can simply click on the Engine Block description and select the block I want to use, and it only lists engine blocks.

Excel cannot evaluate "IF(PartType=A4,PartDescription)". The only option I have found is storing each row as its own named range and using INDIRECT, but that won't work since it will be trying to use the same name for the named ranges. Is this something Excel can't do? Is there a way to get Excel to find the Part Descriptions that match the Part Type for the purposes of a drop-down list? I'm willing to share the actual spreadsheet if that helps. It's a fun sheet to play with if you build engines.

 

[Erik the Awful]

Well, no answers here, but I found the answer here: Create a dynamic dependent drop down list in Excel an easy way
It works in Excel now, but the .xlsx version no longer works in OpenOffice.

I had to add another sheet just so I could get reference-able lists for the drop-down menu. I copied and transpose-pasted the list of engine parts across the top and in the second row I put a FILTER formula and copied it across. "=FILTER(PartDescription,PartType=A1"

Engine Block​	Crankshaft​	Main Bearings​	Main Bolts​
Reuse Stock 305 Block, 3.736" Bore​	Reuse Stock 327 Crank, 3.267" Stroke​	Reuse Stock Bearings​	Reuse Stock Bolts​
Reuse Stock 327/350 Block, 4.000" Bore​	Reuse Stock 305/350 Crank, 3.480" Stroke​	Mahle P-Series Tri-Metal​	Speedmaster Main Cap Fasteners​
Reuse Stock 400 Block, 4.125" Bore​	Reuse Stock 400 Crank, 3.750" Stroke​	​	ARP Main Stud Kits​

Then I went back to the "Engine Build" sheet and opened the Data Validation for the first line and changed it to "=OFFSET('Excel Hack'!$A$1,1,MATCH(A4,'Excel Hack'!A1:AZ1,0)-1)#". I then opened and edited each Data Validation in the column, changing "A4" to match the row number. That fixed it.