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.