ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   auto updating & linking 2 workbooks without opening the linked (https://www.excelbanter.com/excel-discussion-misc-queries/115587-auto-updating-linking-2-workbooks-without-opening-linked.html)

brenner

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

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

brenner

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

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

brenner

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

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

brenner

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


Nabil[_2_]

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


Dave Peterson

auto updating & linking 2 workbooks without opening the linked
 
=match() will return an error if there is no match and a number (an index into
that range) if there is a match.

so

=index('long name'!a:a,match(x99,'long name'!b:b,0))

will look for a match between the value in X99 and column B of the worksheet
with the long name. If there's a match in that column, then the value in column
A of that row with the match will be returned.

I'd build the formula with the other ("Sending") workbook open. Then excel will
adjust the syntax when you close that sending workbook.

Nabil wrote:

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


--

Dave Peterson


All times are GMT +1. The time now is 12:37 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com