Results 1 to 9 of 9
  1. #1
    Senior Member Digby's Avatar
    Join Date
    Nov 2005
    Location
    Tauranga
    Posts
    4,728

    Default 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 ?
    So what colour is your Adkaf?
    Have you joined Proud to Be Kiwi yet?

  2. #2
    Senior Member
    Join Date
    Dec 2004
    Posts
    838

    Default 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. #3

    Default 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. #4
    Junior Member ManUFan's Avatar
    Join Date
    Feb 2006
    Location
    Greymouth
    Posts
    199

    Default 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. #5
    Senior Member Digby's Avatar
    Join Date
    Nov 2005
    Location
    Tauranga
    Posts
    4,728

    Default Re: Any Excel gurus to help with me linking sheets

    Thanks guys I'll try your suggestions
    So what colour is your Adkaf?
    Have you joined Proud to Be Kiwi yet?

  6. #6
    Senior Member Digby's Avatar
    Join Date
    Nov 2005
    Location
    Tauranga
    Posts
    4,728

    Default 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.
    So what colour is your Adkaf?
    Have you joined Proud to Be Kiwi yet?

  7. #7
    Senior Member
    Join Date
    Jan 2005
    Location
    Plimmerton
    Posts
    3,256

    Default 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. #8
    Senior Member
    Join Date
    Dec 2004
    Posts
    838

    Default 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. #9
    Senior Member Digby's Avatar
    Join Date
    Nov 2005
    Location
    Tauranga
    Posts
    4,728

    Default Re: Any Excel gurus to help with me linking sheets

    Quote Originally Posted by Kame View Post
    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
    So what colour is your Adkaf?
    Have you joined Proud to Be Kiwi yet?

Similar Threads

  1. Replicating Text in Excel Sheets
    By caffy in forum PressF1
    Replies: 5
    Last Post: 13-03-2012, 02:25 PM
  2. Combining Excel (07) Sheets
    By taxboy4 in forum PressF1
    Replies: 3
    Last Post: 21-01-2009, 04:33 PM
  3. No of Excel Sheets in a Workbook
    By Brain_Cell_Anon in forum PressF1
    Replies: 7
    Last Post: 01-12-2004, 01:48 AM
  4. Replies: 2
    Last Post: 31-05-2001, 08:31 PM
  5. Dialog sheets in Excel 97
    By in forum PressF1
    Replies: 0
    Last Post: 16-01-1999, 01:26 AM

Posting Permissions

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