Stopping links moving

Microsoft Office excel 2007 - upgrade
February 3, 2011 at 05:51:04
Specs: Windows 7
Linked spreadsheet is saved off each week to keep a record of it, but the link back to the master follows it on, how do I keep it on the original file

See More: Stopping links moving

Report •

February 3, 2011 at 11:45:16
Make believe that we can't see your spreadsheet from where we're sitting, which we can't.

We're going to need a little more of a detailed explanation as to what the "the link back to the master follows it on" means.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

February 4, 2011 at 03:51:30
Ok so in Master Spreadsheet (MS) I have a cell that is the total of all sales in week 1 linked to a sales report in a Sales Spreadsheet (SS). So MS shows summary total sales and SS shows the breakdown. I want to keep a record of week 1 sales so I save the SS as "SS1" and reuse the original SS to enter the week 2 sales. However the link in MS is now pointed to the SS1 instead of SS. How do I maintain the sales data of week 1 without the link following on?
So if link is now .../SS after a copy is becomes .../SS1.
I have copied using MS explorer and opeing the file and using save as.
Thank you

Report •

February 4, 2011 at 06:08:03
If SS will be open whenever you need the link to it to show a value, you can use the INDIRECT function.

The INDIRECT function converts a text string into a reference. It's very powerful, so even if it doesn't work for this particular issue, you should read up on it in the Excel help files and via Google search. It's a great function to keep in your back pocket.

For example, let's say you want to SUM a range of cells, but you want to change that range easily and often, without changing the formula.

In B1 enter 5, in C1 enter 10, in D1 enter this:

=SUM(INDIRECT("A" & B1 & ":A" & C1))

This will evaluate to =SUM(A5:A10)

If you change the values in B1 or C1, you'll change the range that the formula will SUM.

A formula similar to the one below will always refer to the SS workbook since the "reference" is a text string that will never change unless you change it. However, as I said earlier, SS has to be open or the formula will return a #REF error:

=INDIRECT("'C:\Documents and Settings\your_user_name\Desktop\[SS.xls]Sheet1'!$A$1")

If the SS workbook will be closed, there are some sophisticated workarounds, such as found here:

Do a Google search on something like accessing closed workbooks for other suggestions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question