Hi Dave

I have similar problem linking from one workbook to another closed workbook.
The target workbook contains one summary worksheet that is to show an
audience exact values that are generated by formulas in a summary worksheet
on the source workbook, which is on another folder on the network and closed.
The offset does not work on closed source. You have mentioned using
=index('long name'!A:a, match(....)). The long name is the long reference to
the source workbook. What is A:a, match(€¦.)? Could you please let me know in
details how to link cell A1 on the target sheet to cell A1 on the closed
source sheet?

Regards
Nabil


server Could you please

"brenner" wrote:

cheers, i'll have a go!

"Dave Peterson" wrote:

You're gonna have to start with:

=index('long name'!A:a,

then add the match stuff.

brenner wrote:

left rest of formula EXACTLY same just changed OFFSET to read INDEX

whether linked workbook open or closed result = #REF!

"Dave Peterson" wrote:

Share what you tried.

brenner wrote:

thanks for help dave but tried using index and now getting REF.
any other ideas?

"Dave Peterson" wrote:

Nothing you're doing wrong. =offset() won't work on a closed workbook.

Maybe you can replace the =offset() with =index().

=index('long name'!A:a, match(....))



brenner wrote:

=IF(B9="","",OFFSET('C:\Documents and Settings\Administartor\My
Documents\PURCHASE LEDGER BATCH LISTS\Warley\[SupplierCodes 2006.xls]AYRSHIRE
WARLEY'!$A$1,MATCH(B9,'C:\Documents and Settings\Administartor\My
Documents\PURCHASE LEDGER BATCH LISTS\Warley\[SupplierCodes 2006.xls]AYRSHIRE
WARLEY'!$A$2:$A$500,0),1))
I am trying to link two workbooks using the formula above with cell B9 in
the source book being variable. unless i open the linked workbook (which i
would like to mainly leave closed) the result = VALUE. when i open the linked
workbook i get the expected result. don't think i can use lookup because data
within the linked book requires intermittently sorting. sorry for long
explanation BUT - WHAT AM I DOING WRONG?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson