Copying formulas to worksheets

[Amber88]

2nd post... I'm an Excel beginner!

I have a file with four worksheets, expenses by city. I wrote a sumproduct formula in each worksheet to calculate the totals by credit card type, and need to display the totals in the first sheet.

Ideally, I would like to copy the box with the formulas and paste it on the first sheet, calculating the totals from the correct sheet still. My goal is to take the data and past it in, having my formula calculate the totals by card type and display in the first worksheet (I'm a big fan of minimal manual work haha).

Is it possible to define a formula to a worksheet and display in another?

 

[Puuuuma]

There are 2 ways to accomplish this:

1. Define named ranges, in 2007, it is under the Formulas tab, not entirely sure where it is on earlier versions. It allows you to name range and then you can refer to that specific range in formulas using that name - very handy in general, one of my favorite tricks - read about it in the help file.

2. You can reference a range/cell in another sheet by adding the name of the sheet before the refrence, i.e. 'Sheet 1'!A1 (notice the 's and !)refrences cell A1 on Sheet 1. If you click on the cell that you want the formula and type the =, and then change sheets excel will format it correctly.

 

[Amber88]

Thank you so much!!
It's so much easier with a defined range as well, thanks alot for your help!