Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    New Lounger
    Join Date
    Aug 2001
    Location
    New Jersey, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Any suggestions ?


    TIA

    Bill M

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    HTH

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    You can link the flowchart shapes to a cell in the same manner.

    HTH

    Peter

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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.

    Steve

  5. #5
    New Lounger
    Join Date
    Aug 2001
    Location
    New Jersey, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drawing Text Boxes: Show results of formula ?

    Thanks

    Seems like exactly what I am looking for.

    Bill M

  6. #6
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    The difference is that if it is IN the text box (or other object), excel thinks that it is text. If you type "=A1" that is the text excel assumes you want in the text box.

    Starting a FORMULA (eith an equal sign) in the formula bar tells excel that it is a named range.

    Steve

  8. #8
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    I don't understand your question (if you are asking a question?). What do you think might be different about your system?

    Autoshapes, Chart datalabels, chart titles, pictures (objects in general) etc can all be linked to a cell or named cell using the formula bar which is what Bill M had asked for.

    Steve

  10. #10
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    How do you link a regular picture of a cell (or group of cells) without using the formula bar (other than the camera)?

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>

  13. #13
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    just select the object and type "=a1". Don't go anywhere near the formula bar.

  14. #14
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    right. I'll try that one. thanks for the input.

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    Ahh, I see what you were getting at. Excel automatically puts the info into the formula bar if it is a picture!

    All the other shapes allow adding TEXT into the object, so the default is to add it as the VALUE. To make it a formula you must use the formula bar.

    The picture object does NOT have a value, so the default is to add it as a formula.

    I had never tried entering a cell reference to an object without using the formula bar since most don't work!

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •