|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.