Chart Text Box Data Table

  • Thread starter Thread starter Mark Schreiber
  • Start date Start date
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.
 
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.
 
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?
 
A little experimentation revealed that char(9) invokes the tab function. Is
there any way to control how far it tabs?
 
Back
Top