Dear Friends,
I need your punch in my back to help me with my pricelist issue:
I have several suppliers - each with different prices and each with different transport costs to several destination. I think I should you VLOOKUP and pop-up cell and I know how to do it with single supplier, but we I have multiple transport rates from different locations - i'm stuck. I attach my sample schedule - please try to show me the right way. Thank you beforehand!
VLOOKUP.xls
I am not 100% sure that I follow...
but assuming you want to enter something like Goods B in A7 and Mill 2 in A8, then entering this formula:
=INDEX($B$2:$D$4,MATCH(A7,$A$2:$A$4,0),MATCH(A8,$B$1:$D$1,0))
will result in 8 (at the intersection of the inputs).
Similar for other table.
Is that what you meant?
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
My goal is to have price table (schedule) right under first one with formula PRICE + DELIVERY cost. And I need some kind of user-friendly pop-up cell where I can choose destination.
For the "popups", I think you are looking for Data Validation found in the Data menu. From there you select List from allow section, then enter the range your selections are in.
I am not sure I understand yet about the schedule... can you repost the sheet with a sample of what you want to see?
Dear NBVC,
Yes, data validation is smth that I was looking for. Here is what I made (please check attached .xls file).
I have FCA schedule in top left corner - this is prices for different goods at different mills
I have freight rate schedule in top right corner from several mills to different destinations
I have DDP schduele - this is calculation for goods+freight for different mills and destination.
I need to choose destination and get a proper price. I made it for Mill #1 now and I want to ask what is the easiest way use they same formula for the rest of the mills.VLOOKUP.xls
If the table column postions don't necessarily coincide with your price list, then try:
copied down and across the table.
Please Login or Register to view this content.
Dear NBVC,
Thank you! Could you please explain a little bit this functions: index and match. I never used then, but I really want to understand what I am doing.
Here is a very good tutorial... http://www.contextures.com/xlFunctions03.html
Thank you, I found it myself. Could be useful, but for another situations. I need something more "dynamic", so I can change my calculations on spot. In my main worksheet I have more parametrs for final price calculation (like TAX, profit, etc.. ), so using drop-down list looks more effiecient.
Can you upload a more representative workbook then.. remove any confidential data.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks