auto updating & linking 2 workbooks without opening the linked
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
|