Folks,
First time posting so I will make as brief and succinct as possible. Using Excel 2016 desktop version on Windows 10. My question/issue revolves around dynamic named ranges and the ability for them to ignore cells with formulas.
I did some searching and reading and found that using =OFFSET(Data!$C$2,0,0,COUNTIF(Data!$C$3:$C$25,"?*") would ignore blanks and/or cells that have formulas but no data. My attached sample file contains additional information regarding the issue and the bottom line is that if I use line pivot chart with running totals, it includes blank cells in the running total and causes a flat line where the after the last point with data. With a regular chart, I can plot the graduate data with an end and a projection line that begins where that line ends.
So, back to the dynamic range issue that underlies all of this; I can't get the dynamic ranges to correctly ignore those blank cells with formulas and wanted to see if anyone has a suggestion or workaround.
Thanks in advance,
Doug
For Requirement and Graduates, you only want the numeric cells so you can use COUNT:
=OFFSET(Visuals!$D$6,0,0,COUNT(Visuals!$D$6:$D$31),1)
=OFFSET(Visuals!$E$6,0,0,COUNT(Visuals!$E$6:$E$31),1)
For Projection, you could then just use something like:
=OFFSET(Requirement,0,2)
Velouria,
Simple yet elegant and if my knee could bend backwards, I would kick myself in the shorts for not exploring the other COUNT functions. One final question and if I need a new thread I will do so. I want to get a running total of the SUMPRODUCT; I have been using for example: =SUM($B$2:B2 ) and copying that formula down the range of cells. I tried wrapping the SUMPRODUCT in this formula and hitting CTRL-SHIFT-ENTER to make it an array formula but no luck. Suggestions?
maced62
I'd make your data source into a table, called say SourceData, then use a formula like:
=IF(SUMPRODUCT(SUMIFS(SourceData[Grad_Proj],SourceData[Class Year],$B$1,SourceData[Location],$B$2,SourceData[Phase],$B$3,SourceData[Class],$C$6:$C6))=0,"",SUMPRODUCT(SUMIFS(SourceData[Grad_Proj],SourceData[Class Year],$B$1,SourceData[Location],$B$2,SourceData[Phase],$B$3,SourceData[Class],$C$6:$C6)))
