Question about creating a pricing sheet with multiple linked sheets

StephenK

Hello,

New to SmartSheet and have only been through the basic training video.  I understand this product is super powerful and can do just about anything related to spreadsheets.  I just wanted to reach out to the community to see if what I was looking to accomplish is something the group feels is obtainable before I keep diving into further training.

 

We are a food wholesale company and we have a master price list we maintain currently in excel.  From the master list we create 4 (linked to the master) sheets with the identical products except at different price levels created via a formula.

 

In addition to this we would like to create 25-30 (linked to the master) sheets that would contain specialzed pricing or bid sheets for larger clients that would not have our full product offerings but instead a limited selection of products from the master price list.  I saw in the training video that you can setup automatic distribution of certain sheets , so the plan would be to update the master price list every morning and then have these 25-30 specialized sheets update and automatically email out to our clients.

 

Sorry for the long winded question and thanks in advance for any help with this!

 

Stephen

Dave B

If I may offer an alternate suggestion...

 

Create a single sheet and rename the Primary Column to "Item Description".

 

Rename the column to the right of the primary column "Price"

 

Enter an item name in row 1 of the "Item Description".

 

Directly under the item - in the same column - type "Master Price"

 

Enter the master price of the item in the "Master Price" column.

 

Directly under the text "Master Price" (in the Primary column) enter the names of the cllients who purchase this item.

 

To the right of each client name, create the formula to generate each client's price next to their name.

 

Indent these rows so the item becomes the parent row.

 

Do this for each item.

 

You now have a single sheet that includes all your pricing - both master and client in it.  When you update the master price for an item, each client's price will auto-update.

 

Then create a report for each client.  Set it up so that when the primary row contains the supplier name the result is shown.

 

This will produce a report for your clients that includes the partent row (item description) and their pricing for each item they purchase.

 

You can then Share the report with the client (for real-time access) or set up a recurring email to send them a copy at whatever intervals they want.

 

Good luck,

Dave B

StephenK

Dave,

 

Thanks so much for the feedback.  I have a good idea of where you are going with this and I will give it a try.  Would you happen to know fi there is a visual example of this within Smartsheets Support resurces area?

 

Thanks

Stephen

 

Dave B

Sorry, no.  But here are two screenshots of my work.  The first is the sheet, the second is a report generated for one of my suppliers .   "Job#" is the primary column; in this example you see two jobs - 6039 and 6167 - highlighted in yellow.  These are the parent rows.   Under each is an indented row with a supplier name (in your case a client).   Then the supplier name repeats for each item we get from that supplier.   These repetitious instances of the supplier name are required by the report; they force the row to appear on each supplier's report.  They could be in a separate, hidden column to clean up the view a bit, but I show them here so you can see how it works.  Note the dates shown in the parent rows are linked from another sheet (our schedule), and they are copied down into the supplier name row.  This pushes the dates onto each supplier's report, as you can see below.   Hopefully this helps!

 

Dave

 

=================

 

SHEET

 

StephenK

Dave,

 

Thanks so much for taking the tiem to send the screenshots, great help!!

 

Stephen

Dave B

No worries, glad to assist.