Redefine range of cells, array formula returns NA

[Guest]

I'm using an array formula, in which I refer to a named range of cells
"CPLead." From the insert-define menu, I updated the "CP Lead" defintion
from G2:G5000 to G2:G1000, and now my formula returns an NA value. Any ideas
on why the formula now returns this error message? Thanks in advance for
your help!

 

[Chip Pearson]

Post the formula.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Guest]

{=IF($C$3="All Years",SUMIF('Savings Reporting - Bi-Weekly Report
v1.xls'!CPLead,'Savings Reporting'!$D$10,'Source
Data'!I2:I10000),SUM(IF(CPLead='Savings Reporting'!$D$10,IF('Savings
Reporting - Bi-Weekly Report v1.xls'!NegotiationYr='Savings
Reporting'!$C$3,'Source Data'!I2:I10000,0),0)))}

Chip, please let me know if I should elaborate on this. I've developed a
savings spreadsheet where the user can go to drop down (cell C$3$) and select
"All Years", "2007" or "2006." The savings will then populate accordingly,
i.e., if the user selects "2006," the 2006 savings will populate next to each
name.

Thanks again!

 

[Guest]

You reduced the 'height' of your named range by 4000 rows. If your array
formula is using other named ranges, you need to adjust those too.

 

[Guest]

Vergel -

That worked!! Thank you so much! Have a great weekend! - Ashley