Creation of a Sales Order form with links to costing in another sheet

Ken Fagut

I routinely need to build quotes for clients, and there are a couple of issues:

1. Costs related to level of buy-in

2. There are parts families (not all parts are compatible)

It would be great to be able to build a quote through drop down of part and price for the proper rate.

Thanks,

Ken

Find more posts tagged with

Sales

Manufacturing

Cell linking

Account and User Management

Comments

Travis

It sound like you are looking for a VLOOKUP functionality, which we do not support. However, depending on how many parts you have, there may be a workaround I could offer.

Could you provide a little more information about #1, #2, and how many parts you are tracking for each dropdown list?

Ken Fagut

KHS Screen Shot.png

Ken Fagut

Currently there are less than 50 parts to answer yor question above.

Thanks for drilling down on this. Thought I could have pricing based on tier, choose the correct tier then the parts are refered to that price.

Travis

Ken - Without knowing your exact processes or needs, it’s a little difficult for me to get you exactly what you might what. However, I did make a sheet with an example of how this could work for you. In this example I have a Price column that will calculate the cost of an item based on entered quantity (the higher the quantity, the lower the cost). The formula in the Price column checks the quantity and will calculate the cost based on the quantity and pricing level.

Here is the sheet with this example. When you (or anyone else) clicks this link, a copy of the sheet will be added to your account and you will be the owner of it.

https://app.smartsheet.com/b/launch?lx=s5pERcCpr8jL-kRlXa-BOl2F3tjZfBYMXSEruozjq1E

Is this along the lines of what you are looking for?

Ken Fagut

Hi Travis,

This is very cool. From what you have constructed, using this as the back end, how do I have a "quote sheet" as a front end, so that I choose the correct hub motor, battery, docking station, console, parts, etc?

If quantities are the same for all if should pull from that column, correct? Here is a twist, new OEM's that order 300 get the 1000 unit pricing, and those that order 1000, get 2500 pricing?

Thanks

Ken

Travis

Ken, You could use cell linking to the price to a quote sheet or build a report that pulls in the Price and Quantity columns and any other you want to include. There isn’t a VLOOKUP functionality so you are not going to be able to references pricing from another sheet, unless you use cell linking. You might consider building a sheet with all your pricing and formulas, hide the pricing columns, and save it as a template. Then create new sheets from it whenever you need to build a quote.

For new OEMs, you could have a “New OEM” sheet/template and a normal OEM sheet/template or have a something in your sheet that designates them as a new OEM and use an IF statement in your formula to check for that. If they are new, use one set of prices, if they are not new, use another set of prices.

Here is a link to our help center article on formulas: http://help.smartsheet.com/customer/portal/articles/775363-using-formulas

We also have a design desk service where you can set up 30 minute sessions to speak with a product expert who can help you build the solution you are looking for. Here is more information on this: https://www.smartsheet.com/services