Jan 04 2021 06:15 PM
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.
Jan 05 2021 12:43 AM
@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.