| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Linked values not updating | Excel Worksheet Functions | |||
| Linked network workbooks revert to pointing at my hard drive | Excel Discussion (Misc queries) | |||
| Excel auto relinking of workbooks | Excel Discussion (Misc queries) | |||
| auto updating workbooks | Excel Discussion (Misc queries) | |||
| Linking problems between two workbooks - Excel 2000 SP3 | Setting up and Configuration of Excel | |||
Linear Mode
