+ Reply to Thread
Results 1 to 2 of 2

Dynamic linking, formating and variable references

  1. #1
    Forum Contributor
    Join Date
    02-24-2010
    Location
    BC, Canada
    MS-Off Ver
    Excel 2010
    Posts
    170

    Dynamic linking, formating and variable references

    Hi,

    I have three questions about linking to Excel data in Power Point 2013.

    1. Is there a way to have a text field that has text as well as dynamic data? What I'm doing is making a slide with the sentence "This project has another $900 in expenses in 2017." I would like the "900" to be dynamic. I already did that by making text that says "This project has another [blank space here] in expenses in 2017." Then I made a link to the cell with the "900" and put it over top of that text box in the right place. That works, but it's not very pretty, if the value changes to 1,000 then the spacing looks funny. Is there a different way to do this?

    2. One of my slides has a linked Excel value, and in the Excel sheet that cell is a total so it has border formatting on the top and bottom. Is there a way to keep it as a linked cell, but not carry over the formatting? I don't want those lines to show up in Power Point. In Power Point, the border formatting is already set to have no lines.

    3. My Power Point slide has a link to Cell E10 on Sheet3, and updates dynamically as it should. The problem is, if I insert a row into sheet 3, then the value I need moves down to cell E11, but Power Point still looks at E10. Is there a way to link Power Point to a value dynamically? ie, could it do something like VLOOKUP("Total",Sheet3!A:E,5,0)

    Thanks so much, I've spent a few hours googling this to get as far as I've come, but I just can't get past these 3 things.

  2. #2
    Valued Forum Contributor
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    2,068

    Re: Dynamic linking, formating and variable references

    There are some battles that you cannot win and this is one of them
    - a linked cell is a "photo"
    - Excel took that photo
    - the photo is dynamic in Excel
    - PowerPoint has a hot link to a live feed of that photo
    - Powerpoint can squeeze and stretch how it displays the photo
    - PowerPoint can put borders around it and fill it with colour and make it transparent
    - PowerPoint cannot change what is in the photo
    PowerPoint's name may contain the word "power" but it is Excel that wins this particular power struggle

    When linking try to keep everything simple at the Excel end

    Suggestions

    1 Create a separate worksheet for your PowerPoint requirements (sheet PP)
    2 Pull data into that from other sheets by formula
    3 Format cells in PP in the way you want them to appear in PowerPoint (deals with Q2)
    4 To address Q1, build your sentence using concatenation. Get this project has $500 expenses in 2017 from:
    - B3 contains this project has $
    - C3 contains 500 but is pulled in by formula from another sheet and therefore dynamic
    - D3 contains expenses in 2017 with a leading space
    and formula in cell A3 (in sheet PP)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    5 And to avoid the problem in Q3 simply do not insert any rows in sheet PP (and protect sheet PP allowing most editing but disable the row and column insert functions to make sure)
    Last edited by kev_; 02-17-2017 at 03:20 AM.
    To acknowledge help given click *Add Reputation
    To mark thread as SOLVED - see Thread Tools at top of thread
    To attach a file - click REPLY ... GO ADVANCED ... look below for MANAGE ATTACHMENTS etc

    Always ask for an explanation if anything is not clear

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1