| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help Plz
I am having issues with the following formula:
=SUMPRODUCT(('MCO '!$D$2:$D$3000=B$3)*('MCO '!$E$2:$E$3000=$A7)) But if I run this formula and say the data stops at row 600. It changes the fomula to the following: =SUMPRODUCT(('MCO '!$D$2:$D$600=B$3)*('MCO '!$E$2:$E$600=$A7)) I thought about trying to using indirect to fix this but can not make it work any ideas. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help Plz
or a way to look down that same range for just the none blank cells. I just need to make sure it looks down the whole range of row 2 to row 3000 for all non blank cells that might make this easier. or not. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help Plz
What do you mean by " if I run this formula and say the data stops at row 600"?
Where does the formula change? Formula should remain as you typed it irrespective of the last row containing data... What is the Excel version? "Chad Portman" wrote: I am having issues with the following formula: =SUMPRODUCT(('MCO '!$D$2:$D$3000=B$3)*('MCO '!$E$2:$E$3000=$A7)) But if I run this formula and say the data stops at row 600. It changes the fomula to the following: =SUMPRODUCT(('MCO '!$D$2:$D$600=B$3)*('MCO '!$E$2:$E$600=$A7)) I thought about trying to using indirect to fix this but can not make it work any ideas. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help Plz
Excel 2003 and we I say run the formula I mean press F9 to calculate. I
understand that it should not cahnge but it does and I need it not too that is why I am confused and am here asking for help. "Sheeloo" wrote: What do you mean by " if I run this formula and say the data stops at row 600"? Where does the formula change? Formula should remain as you typed it irrespective of the last row containing data... What is the Excel version? "Chad Portman" wrote: I am having issues with the following formula: =SUMPRODUCT(('MCO '!$D$2:$D$3000=B$3)*('MCO '!$E$2:$E$3000=$A7)) But if I run this formula and say the data stops at row 600. It changes the fomula to the following: =SUMPRODUCT(('MCO '!$D$2:$D$600=B$3)*('MCO '!$E$2:$E$600=$A7)) I thought about trying to using indirect to fix this but can not make it work any ideas. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help Plz
It is not changing for me... hence the question...
Try to define a NAME (Insert-Name...) for the range $D$2:$D$3000 and another for $E$2:$E$3000 and use that... Also see if the name also changes... If it does then can you share the file? "Chad Portman" wrote: Excel 2003 and we I say run the formula I mean press F9 to calculate. I understand that it should not cahnge but it does and I need it not too that is why I am confused and am here asking for help. "Sheeloo" wrote: What do you mean by " if I run this formula and say the data stops at row 600"? Where does the formula change? Formula should remain as you typed it irrespective of the last row containing data... What is the Excel version? "Chad Portman" wrote: I am having issues with the following formula: =SUMPRODUCT(('MCO '!$D$2:$D$3000=B$3)*('MCO '!$E$2:$E$3000=$A7)) But if I run this formula and say the data stops at row 600. It changes the fomula to the following: =SUMPRODUCT(('MCO '!$D$2:$D$600=B$3)*('MCO '!$E$2:$E$600=$A7)) I thought about trying to using indirect to fix this but can not make it work any ideas. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help Plz
not able to share the file. classified info. (sorry) I tried using named
ranges but when doing that I get the error of unable to read file. Which reading online indicates could be due to named ranges from sheets that are not the same sheet as the named range "Sheeloo" wrote: It is not changing for me... hence the question... Try to define a NAME (Insert-Name...) for the range $D$2:$D$3000 and another for $E$2:$E$3000 and use that... Also see if the name also changes... If it does then can you share the file? "Chad Portman" wrote: Excel 2003 and we I say run the formula I mean press F9 to calculate. I understand that it should not cahnge but it does and I need it not too that is why I am confused and am here asking for help. "Sheeloo" wrote: What do you mean by " if I run this formula and say the data stops at row 600"? Where does the formula change? Formula should remain as you typed it irrespective of the last row containing data... What is the Excel version? "Chad Portman" wrote: I am having issues with the following formula: =SUMPRODUCT(('MCO '!$D$2:$D$3000=B$3)*('MCO '!$E$2:$E$3000=$A7)) But if I run this formula and say the data stops at row 600. It changes the fomula to the following: =SUMPRODUCT(('MCO '!$D$2:$D$600=B$3)*('MCO '!$E$2:$E$600=$A7)) I thought about trying to using indirect to fix this but can not make it work any ideas. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help Plz
np. define the name at workbook level then it will work. "Chad Portman" wrote: not able to share the file. classified info. (sorry) I tried using named ranges but when doing that I get the error of unable to read file. Which reading online indicates could be due to named ranges from sheets that are not the same sheet as the named range "Sheeloo" wrote: It is not changing for me... hence the question... Try to define a NAME (Insert-Name...) for the range $D$2:$D$3000 and another for $E$2:$E$3000 and use that... Also see if the name also changes... If it does then can you share the file? "Chad Portman" wrote: Excel 2003 and we I say run the formula I mean press F9 to calculate. I understand that it should not cahnge but it does and I need it not too that is why I am confused and am here asking for help. "Sheeloo" wrote: What do you mean by " if I run this formula and say the data stops at row 600"? Where does the formula change? Formula should remain as you typed it irrespective of the last row containing data... What is the Excel version? "Chad Portman" wrote: I am having issues with the following formula: =SUMPRODUCT(('MCO '!$D$2:$D$3000=B$3)*('MCO '!$E$2:$E$3000=$A7)) But if I run this formula and say the data stops at row 600. It changes the fomula to the following: =SUMPRODUCT(('MCO '!$D$2:$D$600=B$3)*('MCO '!$E$2:$E$600=$A7)) I thought about trying to using indirect to fix this but can not make it work any ideas. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help Plz
how do you do that I just learned about naming ranges
"Sheeloo" wrote: np. define the name at workbook level then it will work. "Chad Portman" wrote: not able to share the file. classified info. (sorry) I tried using named ranges but when doing that I get the error of unable to read file. Which reading online indicates could be due to named ranges from sheets that are not the same sheet as the named range "Sheeloo" wrote: It is not changing for me... hence the question... Try to define a NAME (Insert-Name...) for the range $D$2:$D$3000 and another for $E$2:$E$3000 and use that... Also see if the name also changes... If it does then can you share the file? "Chad Portman" wrote: Excel 2003 and we I say run the formula I mean press F9 to calculate. I understand that it should not cahnge but it does and I need it not too that is why I am confused and am here asking for help. "Sheeloo" wrote: What do you mean by " if I run this formula and say the data stops at row 600"? Where does the formula change? Formula should remain as you typed it irrespective of the last row containing data... What is the Excel version? "Chad Portman" wrote: I am having issues with the following formula: =SUMPRODUCT(('MCO '!$D$2:$D$3000=B$3)*('MCO '!$E$2:$E$3000=$A7)) But if I run this formula and say the data stops at row 600. It changes the fomula to the following: =SUMPRODUCT(('MCO '!$D$2:$D$600=B$3)*('MCO '!$E$2:$E$600=$A7)) I thought about trying to using indirect to fix this but can not make it work any ideas. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help Plz
See what the name refers to
I defined to names... one called ColD referring to ='MCO '!$D$2:$D$300 the other ColE to ='MCO '!$E$2:$E$3000 then used the formula =SUMPRODUCT((ColD=B$3)*(ColE=$A7)) "Chad Portman" wrote: how do you do that I just learned about naming ranges "Sheeloo" wrote: np. define the name at workbook level then it will work. "Chad Portman" wrote: not able to share the file. classified info. (sorry) I tried using named ranges but when doing that I get the error of unable to read file. Which reading online indicates could be due to named ranges from sheets that are not the same sheet as the named range "Sheeloo" wrote: It is not changing for me... hence the question... Try to define a NAME (Insert-Name...) for the range $D$2:$D$3000 and another for $E$2:$E$3000 and use that... Also see if the name also changes... If it does then can you share the file? "Chad Portman" wrote: Excel 2003 and we I say run the formula I mean press F9 to calculate. I understand that it should not cahnge but it does and I need it not too that is why I am confused and am here asking for help. "Sheeloo" wrote: What do you mean by " if I run this formula and say the data stops at row 600"? Where does the formula change? Formula should remain as you typed it irrespective of the last row containing data... What is the Excel version? "Chad Portman" wrote: I am having issues with the following formula: =SUMPRODUCT(('MCO '!$D$2:$D$3000=B$3)*('MCO '!$E$2:$E$3000=$A7)) But if I run this formula and say the data stops at row 600. It changes the fomula to the following: =SUMPRODUCT(('MCO '!$D$2:$D$600=B$3)*('MCO '!$E$2:$E$600=$A7)) I thought about trying to using indirect to fix this but can not make it work any ideas. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
