| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 144265 | 2017-08-30 23:59:00 | Any Excel gurus to help with me linking sheets | Digby (677) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 1438683 | 2017-08-30 23:59:00 | 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 ? |
Digby (677) | ||
| 1438684 | 2017-08-31 06:01:00 | 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. |
Kame (312) | ||
| 1438685 | 2017-08-31 06:28:00 | 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 |
Jedsdad (17435) | ||
| 1438686 | 2017-08-31 19:57:00 | =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! |
ManUFan (7602) | ||
| 1438687 | 2017-08-31 23:11:00 | Thanks guys I'll try your suggestions | Digby (677) | ||
| 1438688 | 2017-09-03 10:07:00 | 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. |
Digby (677) | ||
| 1438689 | 2017-09-03 10:35:00 | 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. |
linw (53) | ||
| 1438690 | 2017-09-04 04:29:00 | 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) |
Kame (312) | ||
| 1438691 | 2017-09-05 00:36:00 | 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 |
Digby (677) | ||
| 1 | |||||