How can I use dynamic values from a cell or formula in the parameters window?

Learn how you can load dynamic values from a cell in another sheet or from an Excel formula through the function wizard.

In this article the Trial Balance - Multiple Period and Profit and Loss - Multiple Period DataDear reports will be used as an example - this concept can be applied to most DataDear parameter windows - lookout for these icons image

Step 1: Create a sheet with values

Before generating the DataDear report, you will first need to create a sheet from where the values will be stored and can be loaded.

  1. Open Excel, click on the DataDear tab and login using your email and password.

  2. Create and sheet and rename this sheet to Values (could be any name).

  3. Enter the reporting month in cell A1 and the reporting year in cell B1.
    image

  4. You could also add more values and organise these in a nice template if you plan to distribute this document. For example: (this sample report can be downloaded from the DataDear templates library):

Step 2: Loading the Values

  1. Choose the organisation and select the data table or report such as the Trial Balance - Multiple Period report.

  2. In the parameters window, chose the image icon next to Month to open the window.
    image

  3. Go to the Values sheet (depending on how you renamed this sheet) and select cell A1 and the following cell will be linked =Values!$A$1

  4. Repeat for each condition in the parameter window as in the example below where we are loading the Month and Year from the Values sheet.

  5. You can also use formulas and the functions wizard.
    image

  6. Generate the report by clicking on the Current Sheet button. Next time you need to refresh, change the values in the Values sheet (depending on how you renamed this sheet) and you may do a Quick Refresh or use the Refresh all sheets.

:information_source: Several sheets in the workbook can link to the same values sheet.

Next time you use this report

  1. Open Excel, click on the DataDear tab and login using your email and password.

  2. Change the values in the Values sheet (depending on how you renamed this sheet).

  3. Go to a DataDear sheet, choose the organisation and click on Refresh > Refresh All.

  4. All DataDear sheets will be refreshed with data from the accounting software using the new values.