Log In
Register
Home
August 19, 2016
Offline
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
See Answer
Trusted Members
Moderators
November 1, 2018
Offline
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)
Answers Post
August 19, 2016
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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)))
1 Guest(s)
