auto updating & linking 2 workbooks without opening the linked

G

Guest

=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?
 
D

Dave Peterson

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


=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?
Click to expand...
 
G

Guest

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

Dave Peterson said:
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(....))


=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?
Click to expand...
Click to expand...
 
D

Dave Peterson

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

Dave Peterson said:
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(....))


=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?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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

whether linked workbook open or closed result = #REF!


Dave Peterson said:
Share what you tried.
thanks for help dave but tried using index and now getting REF.
any other ideas?

Dave Peterson said:
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?
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

You're gonna have to start with:

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

then add the match stuff.
left rest of formula EXACTLY same just changed OFFSET to read INDEX

whether linked workbook open or closed result = #REF!

Dave Peterson said:
Share what you tried.
thanks for help dave but tried using index and now getting REF.
any other ideas?

:

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?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

cheers, i'll have a go!

Dave Peterson said:
You're gonna have to start with:

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

then add the match stuff.
left rest of formula EXACTLY same just changed OFFSET to read INDEX

whether linked workbook open or closed result = #REF!

Dave Peterson said:
Share what you tried.

brenner wrote:

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

:

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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Linked Cells, Increment Sheet Number Ref. 3
Linking Groups of cells between workbooks 4
Alternative to SUMif when linking to closed workbooks 8
Open and Close Workbooks 2
I need to change the path of the linked files - thousands of them. 2
PROBLEM WITH WORKBOOKS IN DIFFERENT WINDOWS 8
Linking to other workbooks question 4
Linking Workbooks 2

Top