Jump to content


Photo

Using OFFSET and COUNTA in a dynamic table and chart

OFFSET COUNTA charts tables COUNTBLANK

  • Please log in to reply
2 replies to this topic

#1 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 26 April 2015 - 06:32 PM

I keep track of action items for several departments. The majority of these departments have their action items fall into a certain set of categories. One department, however, has a different set of categories they follow AND a different amount of them.
 
So one department has 14 categories with different names for them (this is the Beta table in the attached file); the rest of the departments have 16 categories and with different names for them (this is table Alpha).
 
I have another table (Omega) that will show the data for a department when that department's name has been selected in cell K1. Omega is what feeds the chart in the file.
 
The names of the categories show up fine with the exception of the bottom two rows showing 0's when the Beta department is selected. I don't want those to show at all.
 
I thought I could solve this by using OFFSET and COUNTA but things are not turning out as I had planned.
 
The attached file has been simplified to account for only two departments.
 
Attached File  switching data table.xlsx   18.91KB   280 downloads


#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 888 posts

Posted 26 April 2015 - 09:56 PM

Hi phoenixrizing,

 

This is actually possible using OFFSET and range names, but it's quite tricky to get it to work correctly.

 

The COUNTA function works great on values that have been typed in, but it will never treat the result of a formula as blank, even if the formula isn't returning a value.  You could work around this using COUNTIF, but I prefer to use COUNTBLANK, which will treat blank formula results as blanks correctly.

 

The formulas for my range names are as follows:

=OFFSET(Sheet1!$H$4,0,0,ROWS(Sheet1!$H$4:$H$19)-COUNTBLANK(Sheet1!$H$4:$H$19),1)

 

Aside from using COUNTBLANK, the technique is identical to the one covered in the video lesson: Expert Skills Lesson 4-6 Create dynamic formula-based range names using the OFFSET function.

 

In order to create the chart, I've created two range names in your workbook, named ChartValueData and ChartCategoryData. It's necessary to do this, as Excel won't work with range names correctly if they are placed in the Chart data range box. To make it work correctly, you must set a range name for both the series values and axis labels.

 

For the video lesson on setting chart data sources, see: Essential Skills Lesson 5-11 Change chart source data.

 

One last thing to be aware of is that this doesn't seem to work very well if the data is inside a Table, as the chart will keep reverting its source data back to ranges if so. To resolve this, I've converted your Omega table back into an ordinary range.

 

For a video walkthrough of converting Tables into ranges, see: Expert Skills Lesson 1-12 Format a table using table styles and convert a table into a range.

 

 

As usual, I have attached a copy of your workbook with the new range names applied to your chart:

 

Attached File  switching data table.xlsx   18.56KB   272 downloads

switching data table.png


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 26 April 2015 - 11:48 PM

Ah, yes, the COUNTBLANK! I forgot about those cells not really being blank. This is great, Jonathan. Thank you very much!







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users