Redefine range of cells, array formula returns NA
 

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!

Redefine range of cells, array formula returns NA
 

Post the formula.


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

"Ashley" wrote in message
...
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!

Redefine range of cells, array formula returns NA
 

{=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!

Chip Pearson" wrote:

Post the formula.


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

"Ashley" wrote in message
...
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!

Redefine range of cells, array formula returns NA
 

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.

"Ashley" wrote:

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!

Redefine range of cells, array formula returns NA
 

Vergel -

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

"Vergel Adriano" wrote:

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.

"Ashley" wrote:

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!