Change Formula with dropdown list

Occasional Visitor

I'm working up a spreadsheet to calculate the weight of different concrete blocks (used as moorings for boats)

Basically I want to have a column for the width and height, and then be able to change the formula based on a selection from a drop down list (the shape of the block - round or square) to calculate the volume, and hence the weight.

I would then add different parameters with things such as chain weights and sea bed surface to calculate the holding capacity of each mooring.

Any advice would be appreciated.

1 Reply

@AngeMelville I would suggest you calculate the weight for each shape (square, rectangular, round etc.) with the given dimensions, in a separate table. Then create a drop-down list that includes all possible shapes. Then pick-up the weight for the selected shape from the "Weights-table", using XLOOKUP,  VLOOKUP or HLOOKUP. Alternatively, create a named range for each of the calculated weights with the exact same name as the shape, and use INDIRECT to return the weight for the selected shape e.g. =INDIRECT(<cell with selected shape>)

Attached you'll find some working examples.