Spreadsheet design notes: Bulk conditions record management

• Build a complete master spreadsheet, and then create scripts.
• Ensure all fields are accounted for in the spreadsheet before mapping scripts to prevent remapping.
• Make sure fields in the spreadsheet are placed in a logical, user-friendly order.
• Use descriptive, friendly label headings for the user. They do not have to match SAP technical names.

Use Excel functionality

Use Excel functions to help eliminate user errors in the spreadsheet.

In the following example, multiple types of data validation and conditional formatting are used to create a spreadsheet that is easy to use and minimizes errors. Note that Winshuttle's products work with all standard Excel functions, including VBA scripts and macros. Although Winshuttle does not provide step-by-step guides for standard Microsoft Excel functions (many sites already offer this), a few key Excel reference sites are listed below for convenience.

Drop down lists

Drop down lists were used for Action (column A) and Condition Types (Column C) in the sample spreadsheet tab AllPrices. A dependent drop down list was used for Key Combo (Column D) due to the dependency on the Condition Type (Column C). This prevents the user from selecting an invalid combination of Condition Type and Key Combo.

Tips for using dependent drop down lists:

For the following 2 tips, please reference this Contextures article.

• First read Using Items with Illegal Characters. In this example K007 was a condition type that needed a dependent drop down list. Note, the named list for the dependence drop down must match exactly the name in the drop down list. However, K007 could not be used as a named list as Excel sees K007 as an actual cell K7 and is not allowed. Secondly K007 must be shown in the Excel file as that is the value that needs to be uploaded to SAP for the script to work. In this case a second look up table was created to hold valid Excel names and using the VLOOKUP function the spreadsheet was able to return K007 value correctly.
• See also Block Changes in the First Drop Down. This prevents the user from changing the first drop down (e.g. condition type) after the second one is selected (e.g. key combo). This again prevents the user from selecting an invalid combination.

Conditional formatting

Conditional formatting was used to highlight required (light blue) and not required fields (dark grey) for each action, condition type, and key combo combination selected. This provides visual indicators to the reader to see what fields they require.

See Use a formula to apply conditional formatting in Microsoft Office Help for how to use a formula to apply conditional formatting.