A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Excel 2007 - Merged cells as data source



 
 
Thread Tools Display Modes
  #1  
Old February 24th, 2010, 05:19 AM posted to microsoft.public.excel.charting
QB
external usenet poster
 
Posts: 150
Default Excel 2007 - Merged cells as data source

I am using a set of merged cells as a data source for a line chart. However,
I am getting empty/null entries as part of the data set from the merged
cells???

Basically, I have a set of merged cells each consisting of 3 cells. When I
create the data source I get values like:
73%,,,61%,,,...

Why is it making up value for each cell even though the cells are merged
together???

I am truly finding 2007 very frustrating and very counter-productive!

Please help,

QB
  #2  
Old February 24th, 2010, 09:28 AM posted to microsoft.public.excel.charting
David Biddulph
external usenet poster
 
Posts: 8,714
Default Excel 2007 - Merged cells as data source

With Excel 2007 as well as with earlier versions the advice stays the same.
Don't use merged cells. These newsgroups show countless reasons why not.
--
David Biddulph


"QB" wrote in message
...
I am using a set of merged cells as a data source for a line chart.
However,
I am getting empty/null entries as part of the data set from the merged
cells???

Basically, I have a set of merged cells each consisting of 3 cells. When
I
create the data source I get values like:
73%,,,61%,,,...

Why is it making up value for each cell even though the cells are merged
together???

I am truly finding 2007 very frustrating and very counter-productive!

Please help,

QB



  #3  
Old February 24th, 2010, 09:34 AM posted to microsoft.public.excel.charting
Andy Pope
external usenet poster
 
Posts: 2,088
Default Excel 2007 - Merged cells as data source

Hi,

Charts do not understand merged cells. Each cell contains a value even
if 2 of those cells are not displayed.

Create a contiguous range of data for the charts.

Cheers
Andy

On 24/02/2010 04:19, QB wrote:
I am using a set of merged cells as a data source for a line chart. However,
I am getting empty/null entries as part of the data set from the merged
cells???

Basically, I have a set of merged cells each consisting of 3 cells. When I
create the data source I get values like:
73%,,,61%,,,...

Why is it making up value for each cell even though the cells are merged
together???

I am truly finding 2007 very frustrating and very counter-productive!

Please help,

QB


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

  #4  
Old February 24th, 2010, 02:14 PM posted to microsoft.public.excel.charting
QB
external usenet poster
 
Posts: 150
Default Excel 2007 - Merged cells as data source

Not an option in this case, I require the formatting to remain (took weeks to
get it functional as is).

So what you are saying is don't use built-in functionalities?! Great,
another great MS'ism! I paid for software, that you are saying doesn't work
properly.

QB




"David Biddulph" wrote:

With Excel 2007 as well as with earlier versions the advice stays the same.
Don't use merged cells. These newsgroups show countless reasons why not.
--
David Biddulph


"QB" wrote in message
...
I am using a set of merged cells as a data source for a line chart.
However,
I am getting empty/null entries as part of the data set from the merged
cells???

Basically, I have a set of merged cells each consisting of 3 cells. When
I
create the data source I get values like:
73%,,,61%,,,...

Why is it making up value for each cell even though the cells are merged
together???

I am truly finding 2007 very frustrating and very counter-productive!

Please help,

QB



.

  #5  
Old February 24th, 2010, 02:21 PM posted to microsoft.public.excel.charting
QB
external usenet poster
 
Posts: 150
Default Excel 2007 - Merged cells as data source

Firstly, thank you for trying to help.

I will lookup/look into 'contiguous range' and see what I can do with that.

Also, as an FYI, I just tested merged cells on Excel 2003 and they work just
fine! It is solely an issue in 2007. Another improvement that was made
between versions by Microsoft...

QB




"Andy Pope" wrote:

Hi,

Charts do not understand merged cells. Each cell contains a value even
if 2 of those cells are not displayed.

Create a contiguous range of data for the charts.

Cheers
Andy

On 24/02/2010 04:19, QB wrote:
I am using a set of merged cells as a data source for a line chart. However,
I am getting empty/null entries as part of the data set from the merged
cells???

Basically, I have a set of merged cells each consisting of 3 cells. When I
create the data source I get values like:
73%,,,61%,,,...

Why is it making up value for each cell even though the cells are merged
together???

I am truly finding 2007 very frustrating and very counter-productive!

Please help,

QB


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

.

  #6  
Old February 24th, 2010, 02:24 PM posted to microsoft.public.excel.charting
Andy Pope
external usenet poster
 
Posts: 2,088
Default Excel 2007 - Merged cells as data source

Hi,

You need to divorce the required data layout for chart with your
'pretty' presentation layout.

If your data is in A1,D1,G1 etc then elsewhere on the sheet, or another
sheet, use formula to link cells.

A10: =A1
B10: =D1
C10: =G1
etc

Then chart A10:C10...

Cheers
Andy

On 24/02/2010 13:14, QB wrote:
Not an option in this case, I require the formatting to remain (took weeks to
get it functional as is).

So what you are saying is don't use built-in functionalities?! Great,
another great MS'ism! I paid for software, that you are saying doesn't work
properly.

QB




"David Biddulph" wrote:

With Excel 2007 as well as with earlier versions the advice stays the same.
Don't use merged cells. These newsgroups show countless reasons why not.
--
David Biddulph


wrote in message
...
I am using a set of merged cells as a data source for a line chart.
However,
I am getting empty/null entries as part of the data set from the merged
cells???

Basically, I have a set of merged cells each consisting of 3 cells. When
I
create the data source I get values like:
73%,,,61%,,,...

Why is it making up value for each cell even though the cells are merged
together???

I am truly finding 2007 very frustrating and very counter-productive!

Please help,

QB



.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

  #7  
Old February 24th, 2010, 02:40 PM posted to microsoft.public.excel.charting
Andy Pope
external usenet poster
 
Posts: 2,088
Default Excel 2007 - Merged cells as data source

I just did a test in xl2003 with Merged and Center Across. Both included
the empty cells in a column chart.

Only way I could get just the value cells was to manually edit the
series formula.

=SERIES(,,(Sheet1!$A$4,Sheet1!$D$4,Sheet1!$G$4),1)

But the series formula has a limit of 1024 characters so is restricted
in the amount of data points that can be specified.

Cheers
Andy

On 24/02/2010 13:21, QB wrote:
Firstly, thank you for trying to help.

I will lookup/look into 'contiguous range' and see what I can do with that.

Also, as an FYI, I just tested merged cells on Excel 2003 and they work just
fine! It is solely an issue in 2007. Another improvement that was made
between versions by Microsoft...

QB




"Andy Pope" wrote:

Hi,

Charts do not understand merged cells. Each cell contains a value even
if 2 of those cells are not displayed.

Create a contiguous range of data for the charts.

Cheers
Andy

On 24/02/2010 04:19, QB wrote:
I am using a set of merged cells as a data source for a line chart. However,
I am getting empty/null entries as part of the data set from the merged
cells???

Basically, I have a set of merged cells each consisting of 3 cells. When I
create the data source I get values like:
73%,,,61%,,,...

Why is it making up value for each cell even though the cells are merged
together???

I am truly finding 2007 very frustrating and very counter-productive!

Please help,

QB


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:43 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.