| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Links different pathing
I have 2 Excel files, sitting in the same folder. There is a formula in file
A that looks for a value in file B and updates file A accordingly. The formula works like a charm when users in my office open file A, select update links, etc. However, when 'remote' users attempt to go thru the same motions, the links error and wont update. I'm sure it's due to the pathing embedded in the formula that gets the user to file B but to no avail have solved for it. Any suggestions. There's got to be a way to make this work. Remote open: =IF(ISERROR(COUNTIF('\\ad1.prod\hig\Shared\Continu ous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D12)),"OpnRISfile",IF(COUNTIF('\\ ad1.prod\hig\Shared\Continuous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D12)0,"Yes","No")) Local open: '=IF(ISERROR(COUNTIF('S:\Shared\Continuous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D15)),"OpnRISfile",IF(COUNTIF('S: \Shared\Continuous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D15)0,"Yes","No")) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Links different pathing
There are some worksheet functions that won't work with closed workbooks--but
IIRC, most of them would return #ref! errors. =sumif(), =countif(), =indirect() Are a few. But there are replacement functions that will work: I'd create this formula with the sending workbook open so that excel can do the heavy lifting and fix the path when you close that sending workbook =if(sumproduct(--('[RIS Compltd.xls]RIS Completed'!$B1:$B1000=D12))0, "yes","no") Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Suzanne wrote: I have 2 Excel files, sitting in the same folder. There is a formula in file A that looks for a value in file B and updates file A accordingly. The formula works like a charm when users in my office open file A, select update links, etc. However, when 'remote' users attempt to go thru the same motions, the links error and wont update. I'm sure it's due to the pathing embedded in the formula that gets the user to file B but to no avail have solved for it. Any suggestions. There's got to be a way to make this work. Remote open: =IF(ISERROR(COUNTIF('\\ad1.prod\hig\Shared\Continu ous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D12)),"OpnRISfile",IF(COUNTIF('\\ ad1.prod\hig\Shared\Continuous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D12)0,"Yes","No")) Local open: '=IF(ISERROR(COUNTIF('S:\Shared\Continuous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D15)),"OpnRISfile",IF(COUNTIF('S: \Shared\Continuous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D15)0,"Yes","No")) -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Links different pathing
Hi Dave. Thank you. I'm fine with having to open both workbooks. However
even when both workbooks are open, the links don't update for the remote user. We're both going to the same files, only getting there a differnt way. When i create the file and input the formula, it works for me, it paths to the file it's trying to read and does fine. However, when someone who opens the file from a different office, the formula can't find the file with the path i input because it's not me accessing. The files havent' been moved. Make sense (i hope). "Dave Peterson" wrote: There are some worksheet functions that won't work with closed workbooks--but IIRC, most of them would return #ref! errors. =sumif(), =countif(), =indirect() Are a few. But there are replacement functions that will work: I'd create this formula with the sending workbook open so that excel can do the heavy lifting and fix the path when you close that sending workbook =if(sumproduct(--('[RIS Compltd.xls]RIS Completed'!$B1:$B1000=D12))0, "yes","no") Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Suzanne wrote: I have 2 Excel files, sitting in the same folder. There is a formula in file A that looks for a value in file B and updates file A accordingly. The formula works like a charm when users in my office open file A, select update links, etc. However, when 'remote' users attempt to go thru the same motions, the links error and wont update. I'm sure it's due to the pathing embedded in the formula that gets the user to file B but to no avail have solved for it. Any suggestions. There's got to be a way to make this work. Remote open: =IF(ISERROR(COUNTIF('\\ad1.prod\hig\Shared\Continu ous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D12)),"OpnRISfile",IF(COUNTIF('\\ ad1.prod\hig\Shared\Continuous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D12)0,"Yes","No")) Local open: '=IF(ISERROR(COUNTIF('S:\Shared\Continuous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D15)),"OpnRISfile",IF(COUNTIF('S: \Shared\Continuous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D15)0,"Yes","No")) -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Links different pathing
Maybe dropping the mapped letter from the link and using the UNC path would
help--especially if the user doesn't map the same letter to that share. But if I were you, I'd still use the alternative formula (with the UNC path). Suzanne wrote: Hi Dave. Thank you. I'm fine with having to open both workbooks. However even when both workbooks are open, the links don't update for the remote user. We're both going to the same files, only getting there a differnt way. When i create the file and input the formula, it works for me, it paths to the file it's trying to read and does fine. However, when someone who opens the file from a different office, the formula can't find the file with the path i input because it's not me accessing. The files havent' been moved. Make sense (i hope). "Dave Peterson" wrote: There are some worksheet functions that won't work with closed workbooks--but IIRC, most of them would return #ref! errors. =sumif(), =countif(), =indirect() Are a few. But there are replacement functions that will work: I'd create this formula with the sending workbook open so that excel can do the heavy lifting and fix the path when you close that sending workbook =if(sumproduct(--('[RIS Compltd.xls]RIS Completed'!$B1:$B1000=D12))0, "yes","no") Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Suzanne wrote: I have 2 Excel files, sitting in the same folder. There is a formula in file A that looks for a value in file B and updates file A accordingly. The formula works like a charm when users in my office open file A, select update links, etc. However, when 'remote' users attempt to go thru the same motions, the links error and wont update. I'm sure it's due to the pathing embedded in the formula that gets the user to file B but to no avail have solved for it. Any suggestions. There's got to be a way to make this work. Remote open: =IF(ISERROR(COUNTIF('\\ad1.prod\hig\Shared\Continu ous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D12)),"OpnRISfile",IF(COUNTIF('\\ ad1.prod\hig\Shared\Continuous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D12)0,"Yes","No")) Local open: '=IF(ISERROR(COUNTIF('S:\Shared\Continuous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D15)),"OpnRISfile",IF(COUNTIF('S: \Shared\Continuous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D15)0,"Yes","No")) -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Links different pathing
Thanks, will do and will let you know how it turns out. I appreciate the
help. Enjoy the day. "Dave Peterson" wrote: Maybe dropping the mapped letter from the link and using the UNC path would help--especially if the user doesn't map the same letter to that share. But if I were you, I'd still use the alternative formula (with the UNC path). Suzanne wrote: Hi Dave. Thank you. I'm fine with having to open both workbooks. However even when both workbooks are open, the links don't update for the remote user. We're both going to the same files, only getting there a differnt way. When i create the file and input the formula, it works for me, it paths to the file it's trying to read and does fine. However, when someone who opens the file from a different office, the formula can't find the file with the path i input because it's not me accessing. The files havent' been moved. Make sense (i hope). "Dave Peterson" wrote: There are some worksheet functions that won't work with closed workbooks--but IIRC, most of them would return #ref! errors. =sumif(), =countif(), =indirect() Are a few. But there are replacement functions that will work: I'd create this formula with the sending workbook open so that excel can do the heavy lifting and fix the path when you close that sending workbook =if(sumproduct(--('[RIS Compltd.xls]RIS Completed'!$B1:$B1000=D12))0, "yes","no") Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Suzanne wrote: I have 2 Excel files, sitting in the same folder. There is a formula in file A that looks for a value in file B and updates file A accordingly. The formula works like a charm when users in my office open file A, select update links, etc. However, when 'remote' users attempt to go thru the same motions, the links error and wont update. I'm sure it's due to the pathing embedded in the formula that gets the user to file B but to no avail have solved for it. Any suggestions. There's got to be a way to make this work. Remote open: =IF(ISERROR(COUNTIF('\\ad1.prod\hig\Shared\Continu ous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D12)),"OpnRISfile",IF(COUNTIF('\\ ad1.prod\hig\Shared\Continuous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D12)0,"Yes","No")) Local open: '=IF(ISERROR(COUNTIF('S:\Shared\Continuous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D15)),"OpnRISfile",IF(COUNTIF('S: \Shared\Continuous Performance Improvement\CE Site Reviews (2007)\ALGL\San Antonio\RIS Compltd\[RIS Compltd.xls]RIS Completed'!$B:$B,D15)0,"Yes","No")) -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Update links box gives Continue or Edit Links dialog | Excel Discussion (Misc queries) | |||
| Edit Links: Changing links on a protected worksheet | Excel Discussion (Misc queries) | |||
| Links: Re-establishing Links | Excel Discussion (Misc queries) | |||
| EXCEL - LINKS cannot easily get list of all links & names in book | Excel Worksheet Functions | |||
| can't update links...can't find links | Excel Discussion (Misc queries) | |||
Linear Mode
