Paste Excel Named Range into Powerpoint

Question

I have Office 2010 on my Windows 7 computer.

On this Microsoft help page http://office.microsoft.com/en-us/powerpoint-help/copy-excel-data-or-charts-to-powerpoint-HA010204555.aspx it says the following is applicable to Office 2007:

• If you want the range of data that you paste as a Microsoft Excel Worksheet Object into a PowerPoint presentation to expand when the corresponding data expands on the Excel worksheet, you can define a name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) for the range of data in Excel before you copy the data. You can then paste a link to the named range by using the Paste Special command. On the Home tab, in the Clipboard group, click the arrow on the Paste button, click Paste Special, and then click Paste link. This works only when you copy data that is not in table format in Excel.

I have tried these exact steps and it doesn't work for me in Office 2010.  Here is my real life example:

I have an excel file with a named range of "Financials" which is defined as "=Charts!$B$2:$E$9".  When I select this range in the Excel file, the named range of "Financials" appears in the selected range box in the upper left corner of Excel (right next to the formula entry area).  I copy it, and follow the directions exactly as above and paste special, "paste link", Microsoft Excel Worksheet (code) Object into Powerpoint.

I then go to my edit links in powerpoint and the cells are hard coded, not in reference to the named range, and this is how the link is:  D:\HFM Input Master\2014 04 22 HFM Prototype Input Master.xlsm!Charts!R2C2:R9C5.

I need to update the Prototype frequently by adding rows.  And the presentation that it is linked to a single powerpoint about 170 times.  So when I insert a row, I don't want to re-imbed 170 different slides. 

Is there any way in 2010 version to link the named range from excel into a powerpoint?  Is there a macro way to update?

Thanks for the help!

Answer 1

MSFT weirdness at it's best!

Strangely I started my sheet names with a C when i tested so that could be it.

Answer 2

The following is a list of syntax rules that you need to be aware of when you create and edit names.

Valid characters: The first character of a name must be a letter, an underscore character (_), or a backslash (). Remaining characters in the name can be letters, numbers, periods, and underscore characters.

Note: You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.

Answer 3

Nice to know but it isn't the problem here.

The name conflict was Sheet names not named range names.

While c and e.g. c1 are not allowed as range names (and Excel will tell you this if you try) the example used (as a sheet name) CAPEX would also be a valid range name.

Answer 4

Hi John-

I think you at least uncovered something.  I tried it in a new file, and got it to work...sometimes.  It worked with Sheet1, Sheet2, etc, but as you said, when you change the sheet name to something else, it may not.

Here is what I tried with sheet names when pasting in a named ranges.  These were the actual sheet names:

ChangedName:  did not paste over named range (only hardcoded range)

ChartsSheet:  did not paste over range (only hardcoded)

Sheet1:  it worked

Sheet2:  it worked

SheetCharts:  It worked

Sheet Space:  It worked

sheet17:  it worked

Shee: it worked

S:  it worked

h:  it worked

hwithmoreletters:  it worked

cwithmoreletters:  did not paste over range (only hardcoded)

ewithmoreletters:  it worked

t with more letters:  it worked

zmicrosoft:  it worked

Charts:  does not work

So, to your point John, it seems to work sometimes, maybe based on the first letter of the sheet name.  It is real weird that this is happening though.  I can probably make it work for myself though!

Thanks for the help!

Answer 5

I was about to say "It works here" but maybe not.

If I use the default name for the sheet (Sheet1, Sheet2 etc ) it seems OK If I rename the sheet to anything else it then fails. Weird or what. Maybe you could confirm this is what you see.