3

I've got this Excel sheet which basically contains a lot of data. Now, this Excel sheet is updated dynamically via a macro that imports the data. So the data might change, meaning, some cells might be populated, while others won't.

So I've got this formula in Sheet 2 in each cell from A2:A60 to M2:M60 which basically looks like this:

=IF(Sheet1!E2<>0;Sheet1!A2;"")

Meaning, if cell E2 on the row I'm in is 0, then the copied value in the new spreadsheet is nothing. Same goes for the next row:

=IF(Sheet1!E3<>0;Sheet1!A3;"")

This is repeated until row 60.

Now, what I want to do is to select the range A2:A60 and insert that data to a chart. But the thing is, the chart adds the cells that doesn't have a value. I want to exclude it from the chart without having to change the range of the chart. Is this possible using a formula in the Chart selection? Or would I have to use a macro?

Edit: Right now, the chart looks like this when I create it based on A2:A60. Notice that only A4:A17 actually have any value, the other ones have nothing, because of the formula described above.

Graph with unwanted (null) values.

Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
Kenny Bones
  • 5,017
  • 36
  • 111
  • 174

1 Answers1

3

You can probably use named range.

You can define names like :

Name    Definition
Date    =OFFSET(Sheet1!$A$1,1,0,COUNTA($A:$A)-1)
Value   =OFFSET(Sheet1!$B$1,1,0,COUNTA($B:$B)-1)

And then use them as a source in your chart.

You can find more information on MS Website.

[EDIT] Here are two other examples:

[EDIT 2] Results of the work within the chat:

Problem: the COUNTA formula did not work on the worksheet because the cells contained formulas so the COUNTA still counted these cells even if the value was empty.

Solution: we used the SUMPRODUCT formula to count the empty values

Name   Definition
Date   =OFFSET(Sheet1!$A$2,1,0,SUMPRODUCT(IF(Sheet1!A2:A60<>"",1,0)),1))
Value  =OFFSET(Sheet1!$B$2,1,0,SUMPRODUCT(IF(Sheet1!B2:B60<>"",1,0)),1))

Regards,

Max

JMax
  • 26,109
  • 12
  • 69
  • 88
  • I don't understand how this is supposed to work. So I create a named range, and then point the chart to that range? – Kenny Bones Jul 06 '11 at 08:59
  • I can't get that to work, while trying to create a named range. I keep getting a "formula error", even though it's exactly like the example above. – Kenny Bones Jul 06 '11 at 09:12
  • @Kenny Bones: do your values begin on A1 or A4 ? what's the name of your sheet ? maybe you'll have to adapt the OFFSET FOrmula ? you can see the range created by the Named Range Formula when you came back to modify the formula, it will display a dotted line around the calculated range, does this part work the right way ? – JMax Jul 06 '11 at 12:43
  • Hmm, when I go to create a new Name, I manually type the formula. So I begin with '=OFFSET(' and continue typing manually the rest. And I see no dotted line around the calculated range. Only when I select that area with the mouse, which makes the formula end up exactly like it does when I type it manually. I actually found an article by Microsoft where I create a spreadsheet which looks the same as the example. And I copy the formula and it also says "formula error". Do you have the possibility to create a sample and save it and upload it somewhere? – Kenny Bones Jul 07 '11 at 07:42
  • 1
    The main issue is to use Named Formulas that were define on a sheet and to tell the graph the whole Named Range, e.g `=Sheet1!Values`. Here is an example: http://depositfiles.com/files/uus1pe0g0 (Excel 2007 - french version but AFAIK, Excel should interpret it for you). And i've edited my post with two more examples. – JMax Jul 07 '11 at 21:08
  • Ok thanx! :) But, the result is still exactly the same as before. The chart is still full of nothing. Meaning, the named range of the actual data contains 60 rows, but only about 12 are populated. And the chart still looks like the example i posted in the first post. – Kenny Bones Jul 08 '11 at 05:44
  • If your named range are ok and the chart still display crap... well, could you send us your file so that we could have a look ? i can't see why it's not working. – JMax Jul 08 '11 at 07:47
  • Is it possible to send personal messages here? This worksheet contains some really sensitive information. And I can't just upload it to tha intharwebz just like that. An e-mail address or something? – Kenny Bones Jul 08 '11 at 07:57
  • i'm afraid you can't: http://meta.stackexchange.com/questions/431/any-way-to-send-a-personal-message-to-another-user. i created a chat room to avoid long discussion in comments: http://chat.stackoverflow.com/rooms/1256/excel-create-chart-from-range-of-cells-while-excluding-null-values. Can you join me there ? – JMax Jul 08 '11 at 08:03
  • I've looked into this now and something really strange is happening. I get an error message saying that the formula is incorrect. This does not happen however if I don't choose to "update the workbook" as soon as I open it. But if I go to check the Named ranges, they formulas are all changed. For example, I went in to specify the named range, and all of a sudden, the 'IF(Tall1!A2:A60<>"";10));1)' is changed to 'IF(Tall1!XFD22:XFD80<>"";1;0));1)' It makes no sense at all! Could this be because I use formulas in the cells I target with the named range as well? – Kenny Bones Jul 08 '11 at 10:13