REGULAR FEATURES EXPLAINED
We very often make similar features for various clients. Even though all spreadsheets are different and custom-made, there are a few features that make regular appearances throughout many spreadsheets. We have two issues, trying to explain how these features work when quoting for a spreadsheet, and then people forget how to use them once they have the spreadsheet. These videos should solve both of those issues. So, if you have a spreadsheet, or are contemplating a quote, here are some regular features, and how they work.
Importing CSV Data
This is usually used when you have data that has been exported from a software programme or website, into a CSV file, which then needs to be ‘read’ by the spreadsheet. The issue here is that the CSV data can change format. Extra unrequired columns could be added, columns could be moved, and headers could change. If you used the direct CSV data, the spreadsheet would then not reference the correct data. Using this format, it adapts to follow the correct data, or it alerts you to missing data, so that you can redirect the spreadsheet to the correct data required.
Linked Data between Multiple Tables
In some cases, data needs to be split over two tabs or data sets. In cases such as continuation of projects, or when jobs are split into different categories to complete different processes, or a number of other applications. The bottom line is that jobs that meet a certain criteria on tab 1, need to then progress to tab 2. The problem is that if they automatically progress, and then more data is added on tab 2, when sorted on tab 1, the correct jobs won’t lie up with the additional data added. So, we have created this kind of ‘semi-automated’ option, where tab 2 notifies you of the new required job, and even tells you what it is. You simply select the job from the drop-down list as prompted. That means that the spreadsheet can match the unique identifies from all tabs to collaborate the data, but each tab runs independently and can be sorted and filtered without affecting the connection between data sets. If you think this might apply to you, or if you’ve been told that this feature will be in your spreadsheet, then the video should explain.
Transferring Data
This is for when you have to transfer data from one spreadsheet to another. It could be used for various issues, but will likely be when a spreadsheet that you are using has been corrupted. Although we try to make spreadsheets as robust as possible, they can become corrupted. If this should happen, the best solution is to simply open a new template of the spreadsheet that has no data, and then transfer your existing data from the corrupted version to the working version. Doing this data transfer is relatively easy, but a process needs to be followed depending on the data sets. This video should explain. So, if you need to transfer your data to the working version of the same spreadsheet, follow this video guide.
Using Filters & Sorters
Most data entry on spreadsheets is done in a table format. Now, we don’t actually use tables (for many reasons) but the data layout resembles that of a table. We then put the filters in place so that you can sort or filter data within that range of cells. Some people try to do a manual sort of data, which then corrupts the data as it does not sort it as a batch. Before sorting or filtering data in your new spreadsheet, please have a look at the correct way to do so. Sorting data also overcomes issues like removing cleared rows for example. Have a look at the video for more information.
Using the Sorter Column
If you have a suspicious looking column with the header of ‘sorter’ on your spreadsheet, which is usually in a different colour, then this explains what it is for. This has been added because you can’t sort by locked columns. So, if you have a range of unlocked columns (for data entry) and then a range of locked columns, you can’t sort by the locked columns. This sorter column allows you to temporarily copy the data from one of the locked columns, so you can sort by that column, and then you can clear the temporary data once done. This can be particularly helpful in the cases where you are using a rolling 12 month period, or need to carry data over. If I have built a columns to indicate rows of data which can be safely removed, then used that column n the sorter column, sort, clear the unrequired rows, and then resort by one of the data columns. That will then clear out all the old unused data from the spreadsheet, to make space for new data.