First Time Macro/VBA User Requesting Assistance

[liquidmettle]

Hello everyone!

Alright, I'm going to explain my situation in length so that its clear what I wish to accomplish. One quick point: If Excel 2013 can make graphs that skip nominally blank cells (they'll have a formula that returns an error value or 0), then the rest of my post is moot. I've never used Excel 2010 let alone 2013, so I am unaware of whether this problem is solved or not. If someone knows conclusively, that's a good enough answer because I'll just upgrade. Otherwise I'm stuck going to macros, the thing I was trying to avoid.

The short of the matter is I am trying to create a dynamic chart (Bar Graph most preferable) that will update its values as they are entered in a different Sheet of the same workbook. However the range is variable (to a certain limit) as data may be added below the data at present, and these new entries need to be counted too.

To my dismay I've discovered Excel 2007 does not filter out N/A's, zeroes or errors from its graphs, so I end up getting all my data crowded at one end of the graph with the "blank" cells filling the rest.

My attempts at creative workarounds:

My workbook is set up with quite a few sheets. Lets say Sheet4's data is being pulled into sheet "J-1." Two columns of data (Columns C and D in J-1) are being copied by a simple formula: this cell in J-1 equals that cell in Sheet4. Column C contains text entries. Column D contains monetary values.

In Sheet J-1, I've used the formula =COUNTIF($C$4:C4,C4) set in column B to give a sequential count next to all text values in column C.

In the next column over, column E, I've used the formula =IF(B4=1,C4,""). This only returns unique text values and skips the duplicates by leaving those cells blank in the column.

Column F then uses =IFERROR(INDEX(Blanks1,SMALL((IF(LEN(Blanks1),ROW(INDIRECT("1:"&ROWS(Blanks1))))),ROW(A1)),1),"N/A") to get rid of all the blanks and return an unbroken column of data.

Column G then uses =IF(F4="N/A","N/A",(SUMIF(C3:C67,F4,D3:D67))) to sum the values of column D for the duplicate entries that have now been removed.

So what I get in Column F and G is two columns of values that will continually update based on what data is entered into Sheet4. I've tried using Cols F+G as my range for the chart but the chart will not skip the ****able blank or error cells when plotting.

1. I know the difference between Na() to return "#N/A", and the "N/A" I've used for cosmetic reasons. I've tried NA(), 0, and "" to produce a null cell in one form or another, hoping the graph would not plot these bars. But alas, it was not to be.

What I need in a macro:

1. Ideally the macro will be automatic without requiring me to press a button or tell the macro to run via the developer ribbon.

2. Will either: hide all "N/A" (or suitable replacement) values by hiding the row they are in (this is acceptable) or otherwise making it so a graph will not count these as data to be plotted.

3. Will unhide these rows as soon as there are no more "N/A's", i.e. the data has been updated and the range of viable data increased.

4. Depending on feasibility I am willing to concede to having the macro slaved to a button that says Update that can update the graph by performing the functions above functions.

5. The macro will need to be able to apply to different ranges of cells. While all condensed data (Like Cols F and G mentioned above) will be in sheet J-1, the different sheets J-1 pulls these data sets from will need to each have their own dynamic graph. Basically the macro just needs to make sure the graph for each sheet skips the n/a etc.


What I will also need:

1. I will need to know how to insert the macro properly. Let's call the Macro HideRows.

2. Do I just copy/paste any provided answers into the VBA editor? What then? Hit the X on the VBA window?

It may seem like I have some knowledge of this process (or not), but thats only because I've spent more time than I care to think about trying to work around this asinine problem of a bar-graph (or any graph other than a line) not being able to skip null values or "blank" cells.


I thank you in advance for getting to the end of this lengthy post, for considering it, and for any help you are able to provide.

Thanks,

-Liquidmettle

 

[Smitty]

Welcome to the Board!

If Excel 2013 can make graphs that skip nominally blank cells (they'll have a formula that returns an error value or 0), then the rest of my post is moot.

Excel (all versions at least since '97) will ignore N/A, but not the text N/A ("N/A"). Instead you can use NA().

=IFERROR(INDEX(Blanks1,SMALL((IF(LEN(Blanks1),ROW(INDIRECT("1:"&ROWS(Blanks1))))),ROW(A1)),1),NA())

=IF(ISNA(F4),NA(),(SUMIF(C3:C67,F4,D3:D67)))

HTH,

 

[dermie_72]

if you don't want N/A's showing, you could use:

If(isna(what is returning the occassional n/a),,(what is returning the occassional n/a))

see if that helps.

 

[liquidmettle]

I appreciate the fast responses but unfortunately that won't work.

I've already used NA() in the formula and the graph still plots the resulting #N/A that was in the cell even though there is no numerical value associated with it in the adjacent column. It simply places as many #N/A's (based on the formula NA() ) below the X-axis as are within its data range.

This is why I believe only a macro that hides that data will work at this point since the hidden data will not be put onto the graph.

I've tried replacing the presently blank cells with every type of different value I can think of (whether using NA(), Zero, or text without quotations to produce a value/name error). They still all get plotted.

Let me know if you need any further information or possibly an example spreadsheet.

 

[liquidmettle]

I have solved my own problem! Better still, without the use of any Macros!

If anyone has been trying to get a Bar Graph to dynamically update without using macros and without it counting blanks, I have a solution (there is only one #N/A marked at the end of the graph and this is acceptable considering it previously graphed all of them).