Question

Click in cell B3, note that you can choose a product from the drop down list. This allows the company to select which product they wish to perform a CVP for. Ensure that calculations will return the correct answers regardless of which product is selected.

In B4 use a formula to retrieve the Unit Sales Price from the Fixed Costs sheet for the product shown in B3.

what is the formula to retrieve the price from two different spreadsheets(i mean in one file) in excel assignment?

Answer 1

the formula is Vlookup

Vlookup (short for 'vertical' lookup) is a built-in Excel function that is designed to work with data that is organised into columns. For a specified value, the function finds (or 'looks up') the value in one column of data, and returns the corresponding value from another column.

the formula you should be using considering, assuming the products are listed in column A of fixed cost sheet and Unit sale price is listed in column B of Fixed cost sheet is=

=vlookup(B3,fixed cost sheet $A:$B,2, 0)

B3= product we are looking for,

fixed cost sheet $A:$B = select columns in which the data is present in fixed cost sheet. in our example the data is present in column A and B. if it is present in column A and D we will select it till D.

2 = the no. of row in which the sales price is given starting from the main row in which product is give. for example of product is in column A and sale price is in column B it will be 2, if it is in C, it will be 3 and so on..

0 = This will take the exact product code from the sheet and look for its reference in fixed cost sheet

Hope its helpful