Charlie has attended:
Excel Advanced course
Spreadsheet design
Thank you for the training course yesterday.
I currently work for a kitchen appliance company that distributes to several retailers. I am designing a sheet that will automatically calculated prices and stock levels.
I have built a dynamic table range that details all the different retailers with have along with the products.
I would like a sheet that will automatically calculated a price when I select a product than a retailer.
Can I do this this IF functions
Regards
Charlie
079 40350079
RE: Spreadsheet design
Hi Charlie
Sounds like you will need to use Vlookup formulas or possibly Index and Match which wasn't probably covered on your training.
To help us decide which one to use please can you say a bit more about the design on your customer / product table. It would help to know the order of the heading. For example
Customer ID, Customer Name, Product, Product Price
Also roughly how you'd like the input sheet layed out would help.
Regards
Doug
Best STL
RE: Spreadsheet design
Hi Doug,
Thank you for the response.
Please find a breakdown below:
column A: Promotion Name (Easter or Black Friday or Boxing Day)
column B: Product (Coffee machine or toaster or kettle,)
column C: Retailer (John Lewis or Amazon or Lakeland)
column D: Retail Price for consumers (same for all retailers)(products different price)
column E: Cost price (various prices depending on product then retailer)(includes margin calculation)
Please find an example below:
A B C D E
Easter Pr001 JL 50 26
Easter Pr002 JL 100 52
Easter Pr001 Ama 50 33
Boxing Pr002 Lake 100 49
Many thanks
Charlie
RE: Spreadsheet design
Thanks for clarifying Charlie.
Here is a suggestion for looking up the data.
Promotion Name ProdRetailer Product Retailer Retailer Price Cost Price
Easter Pr001JL Pr001 JL 50 26
Easter Pr002JL Pr002 JL 100 52
Easter Pr001Ama Pr001 Ama 50 33
Boxing Pr002Lake Pr002 Lake 100 49
Create a column B in your dynamic table data called ProdRetailer which creates a unique reference to lookup.
Formula
=C2&D2
Then on the sales sheet use a Vlookup formula to look up the cost based on that ProRetailer reference value.
The Sales sheet looks like
Date Productid Retailer Cost Quantity Total
26/02/2016 Pr001 Ama 50 2 100
26/02/2016 Pr002 Lake 100 1 100
26/02/2016 Pr001 JL 50 1 50
formula in the Cost column is
=VLOOKUP(B2&C2,Data!B:F,4,FALSE)
You can add an Iferror function to allowe the formula to be copied to blank cells if it helps.
=IFERROR(VLOOKUP(B2&C2,Data!B:F,4,FALSE),"")
Also you can hide the B column on the data sheet for neatness.
Hope that gives you some ideas.
Regards
Doug
Best STL
See attached.
Attached files...
Vlookupdata.xlsxRE: Spreadsheet design
Hi Doug,
Thanks. I think we are getting there.
I can't get the calculations right.
What does the "4" and the B:F refer to in Vlookup calc
Regards
Charlie
RE: Spreadsheet design
Hi again Charlie
The 4 refers to the column offset for the data to be returned from the table starting from the lookup column (B).
I noticed in the example I sent the vlookup is returning the retail price rather than the cost price. The column offfset needs to be 5 and not 4 to return the Cost price.
Sorry for the error.
Regards
Doug
Best STL
