Basic Reporting Part V FAQ: How to Use Cell References

Basic Reporting Part V FAQ: How to Use Cell References

1. In Standard Reports, users can tell a cell to show the value of another cell – for example, you could tell cell A3 to show whatever is in cell D9. A cell’s location—such as A3—is called the cell’s reference.
2. To identify a cell’s reference, simple identify the column letter and row number that the cell appears in. For instance, in the example below, Donation Amount is in cell D10.
3. To use a cell reference, in the cell where you want the data to appear, simply type an equals sign followed by the cell’s address/reference enclosed in square brackets. For example: “=[H3]”. In the example below, we’ve told cell E10 to show whatever is in D10, which is donation amount. When we run the report, the donation amount will show in both Column D and Column E.
4. You MUST use SQUARE BRACKETS. Parentheses and curly brackets do not work.
5. Cell references can be used to do several things. They can, for instance, be used to add a sum/count/average to a Header.
6. In reports, Aggregate formulas (sum/total/count/average/etc.) can only be used in Footers. However, if you would like that aggregate to appear in the header (for example, sometimes it makes more sense to have a total or count at the beginning of a section/above the details instead of after), then you can simply use the cell reference in the header, as pictured below. We could then hide the footer if need be and just show the total in the header.
7. You can use cell references to do math inside a report as well, similar to how you would use Excel. For instance, let’s say you have a financial transaction report and you wish to find out how much each person still owes/has unpaid. To do this, simply create a column called “Balance” (or something similar), and then use cell references to tell the report to subtract the value in the amount paid cell from the value in the amount owed cell, as pictured below.
8. These examples are just the tip of the iceberg. Once you know how to use cell references, they have many uses for quickly and easily doing match inside of reports, displaying data from one area of the report in another, and concatenating cell values.

Save