Change Formula with dropdown list

%3CLINGO-SUB%20id%3D%22lingo-sub-2031560%22%20slang%3D%22en-US%22%3EChange%20Formula%20with%20dropdown%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2031560%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20working%20up%20a%20spreadsheet%20to%20calculate%20the%20weight%20of%20different%20concrete%20blocks%20(used%20as%20moorings%20for%20boats)%3C%2FP%3E%3CP%3EBasically%20I%20want%20to%20have%20a%20column%20for%20the%20width%20and%20height%2C%20and%20then%20be%20able%20to%20change%20the%20formula%20based%20on%20a%20selection%20from%20a%20drop%20down%20list%20(the%20shape%20of%20the%20block%20-%20round%20or%20square)%20to%20calculate%20the%20volume%2C%20and%20hence%20the%20weight.%3C%2FP%3E%3CP%3EI%20would%20then%20add%20different%20parameters%20with%20things%20such%20as%20chain%20weights%20and%20sea%20bed%20surface%20to%20calculate%20the%20holding%20capacity%20of%20each%20mooring.%3C%2FP%3E%3CP%3EAny%20advice%20would%20be%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2031560%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2031989%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20Formula%20with%20dropdown%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2031989%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F917884%22%20target%3D%22_blank%22%3E%40AngeMelville%3C%2FA%3E%26nbsp%3BI%20would%20suggest%20you%20calculate%20the%20weight%20for%20each%20shape%20(square%2C%20rectangular%2C%20round%20etc.)%20with%20the%20given%20dimensions%2C%20in%20a%20separate%20table.%20Then%20create%20a%20drop-down%20list%20that%20includes%20all%20possible%20shapes.%20Then%20pick-up%20the%20weight%20for%20the%20selected%20shape%20from%20the%20%22Weights-table%22%2C%20using%20XLOOKUP%2C%20%26nbsp%3BVLOOKUP%20or%20HLOOKUP.%20Alternatively%2C%20create%20a%20named%20range%20for%20each%20of%20the%20calculated%20weights%20with%20the%20exact%20same%20name%20as%20the%20shape%2C%20and%20use%20INDIRECT%20to%20return%20the%20weight%20for%20the%20selected%20shape%20e.g.%20%3DINDIRECT(%3CEM%3E%3CCELL%20with%3D%22%22%20selected%3D%22%22%20shape%3D%22%22%3E%3C%2FCELL%3E%3C%2FEM%3E)%3C%2FP%3E%3CP%3EAttached%20you'll%20find%20some%20working%20examples.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.