Chart Text Box Data Table

M

Mark Schreiber

For the boss, I would like to display a series of figures (as text) that are
derived from the data underlying a chart. For example, the chart shows
electricity demand in kW over the course of the day. In the upper corner of
the chart, I want to display the total usage in kWhr for the whole day, its
cost, and the percentage difference from our budget. I know how to do this
with just one value: create a text box, then use the formula bar to enter
something like "=Sheet1!$C$4". But to do that with all the information I
want to display would require about a dozen text boxes, and getting them all
to line up & look pretty is doubtful. Is there a way to have such a text box
display multiple lines, or to have it display a range of cells? Thanks. Oh,
Excel2007 by the way. Mark S.
 
S

Shane Devenshire

Hi,

Don't try to modify the text box, instead modify the contents of C4 (in your
example.)

In C4 create a formula such as
=A1&CHAR(10)&B1&CHAR(10)&C1

with A1, B1, C1,... containing the info you want: total usage in kWhr for
the whole day, its cost, and the percentage difference from our budget. I am
using the CHAR(10) to force a carriage return after each component.
 
M

Mark Schreiber

Thanks, Shane. Your solution works well. A follow-on question: Is there a
way to use Char() to effectively set tabs within the text box so the data and
then the units appear is successive, aligned columns?
 
M

Mark Schreiber

A little experimentation revealed that char(9) invokes the tab function. Is
there any way to control how far it tabs?
 
Top