Menu Costing template

%3CLINGO-SUB%20id%3D%22lingo-sub-1441762%22%20slang%3D%22en-US%22%3EMenu%20Costing%20template%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1441762%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20the%20process%20of%20making%20a%20costing%20template%20for%20a%20new%20drinksmenu.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPart%201%20-%20Making%20a%20text%20cell%20represent%20a%20numerical%20value%3C%2FP%3E%3CP%3ENeed%20to%20create%20a%20template%20where%20I%20can%20make%20%3CSTRONG%3Eone%20cell%20with%20text%20equal%20another%20cell%20with%20numerical%20data%3C%2FSTRONG%3E.%20i.e.%20in%20the%20spreadsheet%20A14(text)%20%3D%20B14(numeric)%20-%20but%20still%20display%20both%20(in%20the%20case%20of%20price%20changes).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPart%202%20-%20%3CSTRONG%3ESelecting%20said%20item%20A14(text)%20(from%20drop%20down%20list)%20in%20a%20different%20area%20(K3)%20but%20it%20would%20instead%20act%20as%20B14%3C%2FSTRONG%3E%20(numeric%20value)%20-%20so%20when%20selected%20from%20drop%20down%20menu%2C%20it%20acts%20as%20a%20number(B14)%20rather%20than%20text%20value(A14).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPart%203%20-%20It%20gets%20easier%20from%20here%20(I%20think).%20In%20%3CSTRONG%3EM3%3C%2FSTRONG%3E%20I%20want%20displayed%20a%20%3CSTRONG%3Emultiplication%20sum%3C%2FSTRONG%3E.%20i.e.%20L3*K3%20(50%20is%20entered%20into%20L3).%20Which%20would%20equal%26nbsp%3B%203.00%20(50*0.0600).%20i.e.%20(50XBulleitBourbonPrice%20Incl%20VAT)%20or%20(50*A14(which%20is%20acting%20as%20B14))%20or%20(50X%E2%82%AC0.06).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnd%20Result%20is%20displayed%20in%207R%20X%205C-%3C%2FP%3E%3CP%3EOnly%20data%20entry%20by%20user%20is%20to%20select%20options%20in%20column%20K%20and%20entering%20amount%20in%20column%20L.%20Total%20Price%20for%20all%20ingredients%20can%20be%20easily%20calculated%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1441762%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1442513%22%20slang%3D%22en-US%22%3ERe%3A%20Menu%20Costing%20template%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1442513%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F689628%22%20target%3D%22_blank%22%3E%40Hugh_Butler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20included%20another%20column%20%5Bcolumn%20M%5D%20to%20pull%20the%26nbsp%3BCost%20per%20ML%20(incl%20VAT)%20relating%20to%20each%20product%20using%20index%20and%20match.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(%24B%242%3A%24B%2415%2CMATCH(%24K3%2C%24A%242%3A%24A%2415%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20attached%20workbook%20and%20do%20let%20me%20know%20if%20any%20clarification%20is%20required.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEmmanuel%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

In the process of making a costing template for a new drinksmenu.

 

Part 1 - Making a text cell represent a numerical value

Need to create a template where I can make one cell with text equal another cell with numerical data. i.e. in the spreadsheet A14(text) = B14(numeric) - but still display both (in the case of price changes).

 

Part 2 - Selecting said item A14(text) (from drop down list) in a different area (K3) but it would instead act as B14 (numeric value) - so when selected from drop down menu, it acts as a number(B14) rather than text value(A14).

 

Part 3 - It gets easier from here (I think). In M3 I want displayed a multiplication sum. i.e. L3*K3 (50 is entered into L3). Which would equal  3.00 (50*0.0600). i.e. (50XBulleitBourbonPrice Incl VAT) or (50*A14(which is acting as B14)) or (50X€0.06).

 

End Result is displayed in 7R X 5C-

Only data entry by user is to select options in column K and entering amount in column L. Total Price for all ingredients can be easily calculated

 

 

1 Reply
Highlighted

Hi @Hugh_Butler 

 

I included another column [column M] to pull the Cost per ML (incl VAT) relating to each product using index and match. 

 

=INDEX($B$2:$B$15,MATCH($K3,$A$2:$A$15,0))

 

Please see attached workbook and do let me know if any clarification is required.

 

Cheers,

 

Emmanuel