Drawing Text Boxes: Show results of formula ? (W2

I'm doing some work for which I need to create a drawing (flowchart) with various rectangle objects joined by connectors.
(OK).

What I also need to do (but don't know how) is to have the text in each of the drawing objects be the result of a formula (which might only be a reference to a worksheet cell). (Each drawing object would have a different formula).

Basically what I'm building is a network diagram which shows bandwidth requirements in/out of various nodes. The bandwidth numbers for any particular node are determined by summing some amount of data about processes on that node.

I would like the info on the drawing to update whenever the underlying data changes.

Re: Drawing Text Boxes: Show results of formula ? (W2

Bill,

I can't tell you how to do exactly what you're asking for because I don't know how to do it, but I can offer a workround. Instead of using the flowcharting autoshapes, insert a picture - either use the camera button (View:Toolbars:Customise:Commands:Tools:Camera) or copy a cell, shift + Edit:Paste Picture. Having got the picture object, just select it and type =a1 in it and hit return. this will give you a picture of whats in a1. You'll be pleased to know that connectors work with the picture object, so your only constraint is the fact that it's a rectangle and that may not be what you wanted to use to represent your nodes. Note that if you copy a cell with data in it, that picture is static: changing the cell won't update the picture, so you do need to enter the formula.

Re: Drawing Text Boxes: Show results of formula ? (W2

You can NOT directly put formulas in BUT you can put the results of formulas in it (that are live) by referencing a cell or range name. (This is accomplished by the same way you make live links to chart axes and data labels,etc)

Select the object, and in the "formula bar" (right above cell A1, to the right of the "=") type (no quotes) "=", and then
either:
1) point to the cell whose contents you want in the object
2) type in a valid cell reference (a1, A52 Z63, etc)
3) enter a valid named range

Once they are set as you change the contents of the "links" the text in the objects will change.

The only Problem (and it is minor) is that the object must have all the same formatting as given by the object. Individual parts may not be formatted differently.
If you "link" to cell A1 and A1 has text that has formatting, the formatting is NOT carried into the object.

A tip:
If you want your textbox to have "wrapped text" at particular locations, use Alt-enter while entering text into the linked cell to add a "within-cell" line feed or if the text is concatenated use char(10) to wrap the text in a formula (eg
="LIne1"&CHAR(10)&"line2"

Wrapping done by either of these methods will show wrapping in your object.

Re: Drawing Text Boxes: Show results of formula ? (W2

Well so you can! <img src=/S/blush.gif border=0 alt=blush width=15 height=15> My mistake was to select the flowchart object and try and enter the formula directly, instead of selecting the object and then entering the formula into the formula bar. Quite why this makes a difference is beyond me. I'm assuming from what Steve says that this is the case for everyone and it's not just me?

Re: Drawing Text Boxes: Show results of formula ? (W2

I hear what you're saying, and thanks for the response. But..... I find it odd that a picture responds differently to an autoshape. My question still stands - is this my system (2K/2K) or is it true for everyone?

Re: Drawing Text Boxes: Show results of formula ? (W2

Hmmm. Ok. So I understand they can all be linked using the formula bar (now!) My point is that you don't need to use the formula bar for a regular picture object (hence the reason I didn't think you could do it with autoshapes - never having used the formula bar, I didn't think of doing it that way). This to me sounds like a continuity error in the overall design of excel - albeit one i can live with once aware of it - but I just wanted to check that other people found the same thing to be true.

Re: Drawing Text Boxes: Show results of formula ? (W2

> is this my system (2K/2K) or is it true for everyone?
I think that it is true for "everyone." Works for me in XL97 and XL2002. If you record a macro while doing it, you'll notice that it uses ExecuteExcel4Macro to do it -- even in 2002. Also looks like this is the one exception to the good programming rule of "never use Select." <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> Wonder if it is possible to do it in VBA without selecting the shape?

<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic><small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>