You can try using 3D cell references.
- Select the cell in which you'd like to place the reference. Type
- While holding Shift, click the first sheet and then click the last sheet.
Select the column that you want to reference. You'll end up with something like this:
For example, if you want to sum the contents of column J on all three sheets you can use the formula:
Creating a Name for your 3D Reference
This will make it easier for you to reference the columns.
- Go to Formulas > Defined Names
- Type your preferred name. In the example below, I used "myrange".
- Delete the contents of the Refers to box and type
- While holding Shift, click the first sheet that you want to reference, and then click the last sheet.
- Select the column that you want to reference. In your case, column J. You'll get something like this:
To sum the contents of column J in
Sheet3, you just have to use this formula:
Not all Excel formulas can accept 3D references. Here are the ones that do: