Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
Array Offset() formula with height of 1 returns duplicates? | Excel Worksheet Functions | |||
Array formula returns blank in the cell where it is entered | Excel Worksheet Functions | |||
Redefine Table Array | New Users to Excel | |||
Looking for formula index/match-type that returns an array | Excel Worksheet Functions |