The downloaded file is created as a protected workbook. This means that in order to open the file, the downloading users’ password is required. If you wish to share the file, you can change the password by clicking File > Info > Protect Workbook > Encrypt with Password. Here you can enter a new password or remove the password if desired.
Warning: If you remove the password, this will also remove the requirement for the password to be entered when uploading the file. This means that anyone with access to the Bulk Update tab for your organisation could upload any changes they make to the file.
Understanding the data
Each row is unique to each Location Service Region within your organisation. The columns have been colour coded to represent your organisation (light green), location (orange), location service (yellow) and location service region (light blue). Although the same data may appear across multiple rows, you only need to update the cells in the last row that your organisation appears to change the information. Similarly, for locations and services, make the required changes to the last row where that specific location and/or service appears. This prevents changes being overridden as the data is read from top to bottom during the import process.
Editing the data
When the workbook is opened (assuming a password has been set), a red or yellow notification bar will appear under the menu items (File, Home, Insert etc.). Click the text in this notification bar, then click the Edit option to unlock the workbook for editing.
For better visibility, you can select all cells by clicking the arrow left of column A and above row 1. With all the cells selected, you can double-click the margin between two columns to automatically resize all the cell widths. The same can be down for row heights, by double-clicking the margin between two rows.
Columns with Blue titles are required columns. Without these columns, the file will not pass validation and you will not be able to upload any changes you have made. Please note the following conditional requirements:
- Either the Location address fields and/or the Location Postal address fields are required
- Either a Location_Phone and/or Location_Email is required
Most heading columns have hints (indicated by a small red triangle in the top right corner of the cell), with information regarding what data is required in the column. To view the comment, hover the mouse over the header cell until the comment appears.
Some columns will have a specific set of values that can be entered. These cells will have a downward-pointing triangle appear to the right of the cell when the cell is ‘active’ (an active cell is a cell you have clicked or navigated to). Clicking the triangle will display a dropdown list with available options. If there are more options than can be displayed, a scrollbar will appear in the dropdown list allowing you to scroll through the possible options.
Warning: The dropdown list for Service_Type is dependent on the selection of Service_Category. For this reason, first select a Service_Category and then select a Service_Type.
Example for adding a new service to an existing location
To add a new service to an existing location, you can select the organisation and location cells in the existing row and copy them to the new row to prevent having to fill the data out again. If the row below is blank, you can highlight the cells you want to copy, then holding Ctrl (this prevents numbers and dates from changing), click on the small square at the bottom right of the selected cells and drag it down to the next row. If there is data in the row below, insert a new row between the two and then drag the data you want to copy down to the newly created row.
Now you can enter the new details for the service (yellow columns) and the region (light-blue columns. Once the service fields are completed, select a Service_Region and fill in the age restriction fields (the light-blue columns) if required.
If the service is available in multiple regions, simply copy all the cells, including the service you’ve just added, to a new row and select the next Service_Region relevant to the service.
Once you’ve made your changes, head back to Your Organisation in the Members Centre and select the Bulk Update tab. In the upload section, browse to find the updated file and click ‘Open’. If any changes have been made to your data online since the workbook was downloaded, ensure the changes have also been made in the workbook and tick the ‘Override changes made online since export’ checkbox. If you don’t tick the checkbox and changes have been made online, you will be unable to upload your changes.
Enter the password used to open the document. If the password was removed, you can leave the password field blank. Click ‘Validate’.
If there were any validation errors, you can download the workbook from the Validation Results screen and the invalid cells will be highlighted to easily find and make the corrections required. All your changes will remain in the validation result file so you can upload this file when re-validating.
If your validation passed and there were changes made, you can continue to upload the data. This will only upload the rows that have changes and you can see which rows are being uploaded during the upload process. If any errors occur during the upload, the log will display which row and cell the error occurred in. You can make the changes as required and validate (upload) the file again.
- Don’t have a password?
Users who login with external logins (such as Google) may have not set a password. To set a password, navigate back to the Dashboard, click ‘Update your details’, then at the top-right click on your username/email address and select ‘Change Password’ from the menu. Now you can set a password.
- Can’t change the Service_Type?
Ensure you have selected a Service_Category for the row. Select a cell in the Service_Type column (not the header cell). In the top menu items, click ‘Data’, then click ‘Data Validation’ > ‘Data Validation…’. When the dialogue box appears, tick the ‘Apply these changes to all other cells...’ checkbox then click ‘OK’. You should now be able to select an option.