Help in Excel

[SolkaTruesilver]

Hello there.

I am trying to include a linked chart from excel file X into excel file Y. (Problem 1)

The Excel file X is made from a template, and all the elements in it has standarized denomination.

I would like to be able to change the source of the link from file X to file Z, so the chart I linked into file Y would change from Table3 in file X to Table3 in file Z. (problem 2)

can someone help me?

[Brother Oni]

Note that all solutions may be dependent on what version of Excel you're using.

Problem 1: Copy the chart from file x (either use CTRL-C or Copy from the Edit menu), then paste (CTRL-V or Paste from the Edit menu) into file y.

The source data for the chart is still linked to the data from file x (don't rename or move the file, else the linking will break).


Problem 2: The easiest way would be to move file x into a different directory, then rename file z to the exact name as file x. File Z needs to have all data cells in the exact same location as file X though, else things will break.

The next easiest (and one I recommend) would be to reformat the contents of file z into the same layout as file x, then copy and paste that data as a new chart into file y.

Since file x is a template, you can copy the data from file z and paste into a blank version of x using the Paste Special command and selecting Values only or use Destination Formatting.

[SolkaTruesilver]

Thanks a lot! That helps!

But now, I have another problem. I don't seem to be able to paste-link a chart from an excel workbook to another. When I select "Special Paste --> Paste Link", it gives me an error message saying "Unable to Paste Link".

Any idea how to solve that?

[Brother Oni]

That's because you just need to Paste, rather than Paste Special for the chart.

Paste Special only tends to be used for data rather than charts - the source data for the chart is auto-linked to the data in the original file, at least it is in Excel 97 and Excel 2003.

Another thing to check - are your file paths/file names too long? If you're using Windows (which uses Joliet by standard), I think you have a limit of 100-odd characters for the entire path.

[SolkaTruesilver]

But if I paste, it pastes with an empty graph. The source data is absent...

Hum... so it's not possible to paste a graph that is linked to another graph?

Darn it. I love/hate excel.

[Brewdude]

it's time for you to invest in a book. At some point, you stop being able to do what you want to do without visual basic, so best to just use standardized industrywide known cheats (and you know they are known industry wide because someone put them in a book!) rather than learn visual basic all on your lonesome.

[Brother Oni]

But if I paste, it pastes with an empty graph. The source data is absent...

Hum... so it's not possible to paste a graph that is linked to another graph?

Darn it. I love/hate excel.

That's odd. I've just tried copying a graph to a new workbook, then copying THAT graph to a different third workbook and it still works.

Right click on the blank chart, go to Source Data, then try browsing through to the file and data cells you need.

However, it may be easier to link the new graph to the original source data for the first graph or copy and link the original source data to the new workbook and create a new graph from that.

I've just tried this out in Excel 97 - what version are you using?


At some point, you stop being able to do what you want to do without visual basic, so best to just use standardized industrywide known cheats (and you know they are known industry wide because someone put them in a book!) rather than learn visual basic all on your lonesome.

That involves macros being enabled on the workbook, which may not be possible depending on his security settings.

That said, Solka does seem to be using a very convoluted way of linking graphs from data (a graph to a graph from the source data? Why not just use the source data?), so he basically has 3 options:

Simplify down what he's trying to achieve,

Re-organise everything so that it works properly,

Learn a scripting language that will do everything.