In my April 25th, 2022 article, I discussed with you the Office Wizard in CELCAT Timetabler; which is a great tool for extracting statistics from your CELCAT database into a Microsoft Excel spreadsheet or a CSV file. If you would like to review that article, click here.

However, one small issue with the Office Wizard is that the output is always presented in a specific way and, unfortunately, there are no tools within the CELCAT user interface to modify that format. This could be a little frustrating if you regularly produce the same report, such as a room utilisation spreadsheet for the same set of rooms each month. As things currently stand, you will run the report and then, within Excel, you’ll have to manually go in and change things (i.e. the utilisation figures from decimals to percentages). Also, before you present the report, things like the font type, cell colouring, etc. will have to be modified to make it more presentable.

A solution is to use the linked cells functionality within Microsoft Excel to create a spreadsheet, with all your formatting preconfigured, that automatically loads and presents the relevant data in a newly created file from the CELCAT Office Wizard. You may still have to change some minor things (such as the month heading, as this data isn’t exported from the Office Wizard), but you can drastically reduce the amount of time needed to format a report to your liking.

I’ll briefly describe the process here. You can also watch a short video embedded below or, when you’re in Excel itself, search for “create linked cells in another workbook” in the Microsoft Excel Help.

Essentially, this method refers to the contents of cells in another Excel spreadsheet file (a workbook) by creating ‘external reference formulas’, more commonly called links. These links can reference a cell, or a range of cells, within that workbook. Here’s how to do it:

  1. Open the workbook that will contain the external reference (the destination workbook) and the workbook that contains the data you wish to link to (the source workbook).
  2. Select the cell, or cells, in the destination workbook which you want to display values from the source workbook.
  3. In the formula box/bar, type the equal sign symbol (=). If you wish, you can use an Excel function here, such as SUM, by typing the function name followed by an opening parenthesis. For example, =SUM(.
  4. Switch to the source workbook and select the worksheet containing the cells you wish to link to the currently active cells in the destination workbook.
  5. Select the cell, or cells, you wish to be referenced and press Enter.

Note: If you select multiple cells (i.e., =Sheet1!A1:A10) in a current version of Microsoft 365, then you can simply press Enter to confirm the formula (as a dynamic array formula). Otherwise, the formula must be entered (as a legacy array formula) by pressing Ctrl+Shift+Enter. For more information on array formulas, see ‘Guidelines and examples of array formulas’ in the Microsoft Excel Help.

  1. Excel will return you to the destination workbook where the cells you selected will now display the values from the source workbook (or, if you chose to use one, the results of the Excel function applied to those cells).

Now, if you overwrite the source workbook with a newly created one and open the destination workbook, you will see the linked cells in the destination workbook will have changed to reflect the differences in the newly created source workbook.

A couple of useful things to note here.

Firstly, as Excel returns the link with absolute references, if you need to copy the formula to other cells, you’ll need to remove the dollar signs ($). So, in my example, I would modify =[SourceWorkbook.xlsx]Sheet1!$A$1 to =[SourceWorkbook.xlsx]Sheet1!A1 .

Secondly, when you close the source workbook, Excel will automatically append the file path to the formula in the destination workbook. So, in my example, the formula changes from =[SourceWorkbook.xlsx]Sheet1!$A$1 to ='C:\Reports\[SourceWorkbook.xlsx]Sheet1'!$A$1 .

I hope you find this information makes the CELCAT Timetabler's Office Wizard an even more valuable tool.

Tags

Related Articles