Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Oct 2006
    Location
    McLean, Virginia, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Link Excel data to autoshapes (2003 SP2)

    I am working on a Bar of Pie chart and am having trouble fitting the text I want in the bar portion labels. I have never been able to stretch the size of the label to fit the text I want.

    This is my workaround: way back in Excel 2000 I found directions to do the link: choose the shape, click the Formula bar and enter the formula that links to a worksheet cell. I can do that to link only one cell at a time. How do I write the formula to link the data from 4 cells-- 2 text cells, a percent cell and a value cell--into one autoshape???

    TIA,
    Georgette

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link Excel data to autoshapes (2003 SP2)

    Create a formula in another cell that returns the text you want, e.g. if you want to combine A1, B1, C1 and D1, put this formula in E1 (or another cell).

    =A1 & " " & B1 & ": " & C1 & ", " & D1

    (The spaces around & aren't needed, but I included them for readability)

    You can now use =E1 as formula for the autoshape.

  3. #3
    New Lounger
    Join Date
    Oct 2006
    Location
    McLean, Virginia, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link Excel data to autoshapes (2003 SP2)

    Neat, but I still have a formatting problem. A1 is a text cell, B1 is a numerical cell formatted as a percent and C1 is a numerical cell formatted as money.

    The formula works but the percent cell comes through as its numerical equivalent (0.33057423697403 instead of 33%) and the money cell does not have a dollar sign (but this I can add in the formula).

    How do I get the % cell formatting to "stick"??

    TIA,
    gg

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link Excel data to autoshapes (2003 SP2)

    Use the TEXT function in the formula, e.g. replace B1 with TEXT(B1, "0%") and C1 with TEXT(C1,"$#,##0.00")

  5. #5
    New Lounger
    Join Date
    Oct 2006
    Location
    McLean, Virginia, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link Excel data to autoshapes (2003 SP2)

    SWEET!!! Just needed to know the "language" to make it work.

    Thank you so much. Next year we will just have to update the numbers and the graph will change.
    gg

Posting Permissions

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