| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change Links/SumIf function
Hi there, I have a sumIF lookup adding tabs in another worksheet called 2006 Production Grid. SUMIF('[2006 Production Grid 8.01.06.xls]Q1'!$A:$A,$B11,'[2006 Production Grid 8.01.06.xls]Q1'!I:I)+SUMIF('[2006 Production Grid 8.01.06.xls]Q2'!$A:$A,$B11,'[2006 Production Grid 8.01.06.xls]Q2'!I:I)+SUMIF('[2006 Production Grid 8.01.06.xls]Q3'!$A:$A,$B11,'[2006 Production Grid 8.01.06.xls]Q3'!I:I)+SUMIF('[2006 Production Grid 8.01.06.xls]Q4'!$A:$A,$B11,'[2006 Production Grid 8.01.06.xls]Q4'!I:I) Unfortunately, the title of the Production Grid changes constantly to reflect the date, so I need to update links and update the name of the sheet for my formulas to work. When I do this, all of my lookup formulas except my sumIF work. SumIF only works if the Production Grid document is open. Any idea why? Jill -- jillteresa ------------------------------------------------------------------------ jillteresa's Profile: http://www.excelforum.com/member.php...fo&userid=9498 View this thread: http://www.excelforum.com/showthread...hreadid=567115 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Change Links/SumIf function
That's just the way it works. Try SUMPRODUCT
SUMPRODUCT(--('[2006 Production Grid 8.01.06.xls]Q1'!$A:$A=$B11),'[2006 Production Grid 8.01.06.xls]Q1'!I:I) etc. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "jillteresa" wrote in message ... Hi there, I have a sumIF lookup adding tabs in another worksheet called 2006 Production Grid. SUMIF('[2006 Production Grid 8.01.06.xls]Q1'!$A:$A,$B11,'[2006 Production Grid 8.01.06.xls]Q1'!I:I)+SUMIF('[2006 Production Grid 8.01.06.xls]Q2'!$A:$A,$B11,'[2006 Production Grid 8.01.06.xls]Q2'!I:I)+SUMIF('[2006 Production Grid 8.01.06.xls]Q3'!$A:$A,$B11,'[2006 Production Grid 8.01.06.xls]Q3'!I:I)+SUMIF('[2006 Production Grid 8.01.06.xls]Q4'!$A:$A,$B11,'[2006 Production Grid 8.01.06.xls]Q4'!I:I) Unfortunately, the title of the Production Grid changes constantly to reflect the date, so I need to update links and update the name of the sheet for my formulas to work. When I do this, all of my lookup formulas except my sumIF work. SumIF only works if the Production Grid document is open. Any idea why? Jill -- jillteresa ------------------------------------------------------------------------ jillteresa's Profile: http://www.excelforum.com/member.php...fo&userid=9498 View this thread: http://www.excelforum.com/showthread...hreadid=567115 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Change Links/SumIf function
Thanks for your help. I tried the formula and could not get it to work, even when simplifying to one tab. Any other suggestions? Jill  -- jillteresa ------------------------------------------------------------------------ jillteresa's Profile: http://www.excelforum.com/member.php...fo&userid=9498 View this thread: http://www.excelforum.com/showthread...hreadid=567115 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Change Links/SumIf function
Sorry my mistake. SUMPRODUCT doesn't work with complete columns, you have to
specify a range. SUMPRODUCT(--('[2006 Production Grid 8.01.06.xls]Q1'!$A1:$A100=$B11),'[2006 Production Grid 8.01.06.xls]Q1'!I1:I100) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "jillteresa" wrote in message ... Thanks for your help. I tried the formula and could not get it to work, even when simplifying to one tab. Any other suggestions? Jill -- jillteresa ------------------------------------------------------------------------ jillteresa's Profile: http://www.excelforum.com/member.php...fo&userid=9498 View this thread: http://www.excelforum.com/showthread...hreadid=567115 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Change Links/SumIf function
Thanks - this worked perfectly. -- jillteresa ------------------------------------------------------------------------ jillteresa's Profile: http://www.excelforum.com/member.php...fo&userid=9498 View this thread: http://www.excelforum.com/showthread...hreadid=567115 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| numerical integration | Excel Discussion (Misc queries) | |||
| Can function in one cell change value or function in another cell? | Excel Worksheet Functions | |||
| how do I change the fill collor os a cell using a function in VB | Excel Discussion (Misc queries) | |||
| Custom Function not being auto calculated when cells change..help? | Excel Worksheet Functions | |||
| change function variable prompts?? | Excel Worksheet Functions | |||
Linear Mode
