1. ## Any Excel gurus to help with me linking sheets

Hello

I have a project to do which requires me to do a SumIf formula on one sheet (sheet2) and have the answer appear on another sheet. (sheet1)

I can do the Sumif formula on sheet 2 all ok.

But I cannot work out the syntax to do this formula on sheet 1 and have it refer to the data on sheet 2.

Any suggestions ?

2. ## Re: Any Excel gurus to help with me linking sheets

I'm just going to take a stab at this but copy the cell the formula is in then paste special (one of the options should keep references) into the sheet you want it in. If that does not work then...

create the formula on the sheet you want and when you need data from the other sheet switch to it and select the cell, this should also show you how to reference the other worksheet.

3. ## Re: Any Excel gurus to help with me linking sheets

Have you tried clicking on the cell where you want the answer to appear (Sheet 1) the clicking the up arrow on the right hand side of the Function Argument window.
Then you should be able to switch to the other sheet (Sheet 2) and select the Cell or cells required.
You should be able to do this for all 3 range, criteria, Sum-range if required

You know of course you can rename each Tab to make it easier to understand what each sheet is being used for

4. ## Re: Any Excel gurus to help with me linking sheets

=SUMIF(Sheet2!B2:B7,">0") - sums all the numbers in sheet 2 in the range B2-B7 which > 0. This result (formula) is in Sheet1 Cell B2 (for example).

The important thing is the sheet reference "Sheet2!B2:B7" - sheets are referenced by the name then ! followed by the range.

Using \$ provides absolute referencing e.g. \$B\$2:\$B\$7 would reference B2:B7 all the time no matter where the formula was copied to.

Otherwise the range would change to B3:B8, B4:B9, etc as the formula is dragged down the cells. Useful if you want
to sumif the same range but with different criteria.

Hope this helps!

5. ## Re: Any Excel gurus to help with me linking sheets

Thanks guys I'll try your suggestions

6. ## Re: Any Excel gurus to help with me linking sheets

Hi Guys
I'm still having problems.

I can do the sumif formula on the sheet2.
But I am still having troubles trying to enter it on Sheet1
Here is my formula on sheet1 where sheet2 is "items"

=SUMIF(Items!A3:A9,A4,C3:C9)

I cant see why it does not work - all I get is \$0.00 in the cell.

Thanks Manu, to remind me to use absolute cell references. I will do that one I get the formula working in one cell.

Any suggestions. I did check to make sure the cells are formatted to numbers.

7. ## Re: Any Excel gurus to help with me linking sheets

I have done this. In the receiving cell you put =\$sheetname.cellref.

e.g. \$Invest.B17 This will go to sheet Invest and copy the cell contents.

I hope I have not misinterpreted your question.

8. ## Re: Any Excel gurus to help with me linking sheets

Each area you reference maybe from Items, so you would need to specify each place.

e.g.

=SUMIF(Items!A3:A9, Items!A4, Items!C3:C9)

9. ## Re: Any Excel gurus to help with me linking sheets

Originally Posted by Kame
Each area you reference maybe from Items, so you would need to specify each place.

e.g.

=SUMIF(Items!A3:A9, Items!A4, Items!C3:C9)
Yes, I tried that and it worked first time!!

Thanks a lot

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•