When running sourcing events with several worksheets, you may want to be able to use data from one worksheet and another. Linked worksheets with lookup column, lookup value, and lookup field allows you collect data from previous worksheets and migrate it to a new worksheet. If you have a complex worksheet with fifty or more columns to reflect different cost components, through linked worksheets you can break out the general components into different sheets and have a summary worksheet that just has the high level pricing.

If you do not see the option to create Linked Worksheets, reach out to your CSM to ensure that configuration is set up appropriately. Linked Worksheets help build more flexible bid sheets to capture supplier’s responses. To analyze the responses utilizing Linked Worksheets, the DNA functionality also needs to be configured and turn on. If you don’t want to use DNA, you can export the data into Excel to continue your analysis.

Why use Linked Worksheets?

With the new functionality of linked worksheets, this allows users to manage complex pricing in a single place, connect your data across sheets, and simplify a complex RFx by breaking it into pieces. 

Below, we have shared several very common use cases of when to use linked worksheets:

• Freight costs
• Collect freight costs per pound to various locations and use the pricing to calculate fully landed cost of each item. Prices will be the same across the different items
• Project costs
• Collect rate cards on one sheet and then pick and choose titles and quantities on a second sheet to build the cost of a project
• Facility information
• Collect general information about production facilities on one sheet and then use that information on a second page

• Currency conversion

• Store rates on worksheet and run conversions on a second sheet

Building Linked Worksheets

In the following example to build linked worksheets, we will be using linked worksheets to convert different currencies on to our bid sheets.  

You can do this in just seven easy steps: 

Step 1 
Within an Event go to Add a New Worksheet: 

Step 2
Make one Worksheet and manually insert the conversion rates that you believe your Suppliers may require. 

(Example: If you are an American Buyer and want to base the conversion rate on U.S. Dollars, you manually enter that one Euro is equivalent to $0.89) For this example we will be using US Dollars as our reference point:

Step 3
Create your bid sheet

Step 4
Then add and edit a column for Suppliers to fill out their quoted price: 

Step 5
Add a column called Currency, make the column a lookup type, link the source worksheet as the Conversion Rates Worksheet you created earlier, and lastly make the Source Link Column the one that has the title of your differing Currency Types. 

It is important to note that the functionality does not work correctly if the source column (in this case, Currency Type) is hidden from the suppliers. It is important for suppliers to see the values in the dropdown.

(I.E. US Dollars, English Pounds, All the Currency Types you entered in your Conversion worksheet)

Step 6 
Then you are going to create one more column called Price in Dollars, and set that column as a Formula Column, then click Edit Formula.  

Step 7
Within the edit formula window, you are going to set up the formula that is going to convert the Supplier’s quoted price from their currency into dollars. This can be done by multiplying the quoted price by Source Field, or by Source Value. 

To do this you will take the Quantity of the item multiplied by the Quoted Price Multiplied by either Source Field, or by Source Value. 

•  Source Field is used if the value depends on a dropdown value in the spreadsheet 

• Source Value is used for the same value used for the entire column. 

To view more information on how to set up Formulas within Scout, please go to the following articles:  

New Worksheets: Formulas

Classic Worksheet Builder: Advanced Formulas