Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Issues referencing range between workbooks
Hi All,
Maybe I am just missing something really simple (most likely) so forgive me if I seem a bit slow. I started of with a single workbook, the first worksheet had a matrix of figures in it, the next several tabs were filled with formulas and macros to gather statistics and generate metrics. Due to some performance issues and because I wanted the first sheet to be able to be shared across several users, I split up the workbook, having the Matrix worksheet in the first book (shared), and the other worksheets in an Admin workbook. The issue is that if I, the admin, open up the Matrix first, then open the Admin workbook, all the admin formulas work. HOWEVER!!! If I open up the Admin workbook alone, all formulas appear as #VALUE! All admin formulas are using a named range. The range "status" is specified as: ='C:\Documents and Settings\[user]\Desktop\Projects\[filename.xls]Worksheet1'!$F$12:$Y$236 An example formula is: =COUNTIF(status,"p*") Is this a known issue? Or am I just missing something completely obvious? Cheers Rob |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Issues referencing range between workbooks
There are functions that don't work with closed files.
=countif(), =sumif(), =indirect() are a few. Maybe you could use: =sumproduct(--(left(status,1)="p")) =sumproduct() won't work with whole columns--but that's not a problem with your name. Rob Manger wrote: Hi All, Maybe I am just missing something really simple (most likely) so forgive me if I seem a bit slow. I started of with a single workbook, the first worksheet had a matrix of figures in it, the next several tabs were filled with formulas and macros to gather statistics and generate metrics. Due to some performance issues and because I wanted the first sheet to be able to be shared across several users, I split up the workbook, having the Matrix worksheet in the first book (shared), and the other worksheets in an Admin workbook. The issue is that if I, the admin, open up the Matrix first, then open the Admin workbook, all the admin formulas work. HOWEVER!!! If I open up the Admin workbook alone, all formulas appear as #VALUE! All admin formulas are using a named range. The range "status" is specified as: ='C:\Documents and Settings\[user]\Desktop\Projects\[filename.xls]Worksheet1'!$F$12:$Y$236 An example formula is: =COUNTIF(status,"p*") Is this a known issue? Or am I just missing something completely obvious? Cheers Rob -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Issues referencing range between workbooks
Some more info.
=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 Rob Manger wrote: Hi All, Maybe I am just missing something really simple (most likely) so forgive me if I seem a bit slow. I started of with a single workbook, the first worksheet had a matrix of figures in it, the next several tabs were filled with formulas and macros to gather statistics and generate metrics. Due to some performance issues and because I wanted the first sheet to be able to be shared across several users, I split up the workbook, having the Matrix worksheet in the first book (shared), and the other worksheets in an Admin workbook. The issue is that if I, the admin, open up the Matrix first, then open the Admin workbook, all the admin formulas work. HOWEVER!!! If I open up the Admin workbook alone, all formulas appear as #VALUE! All admin formulas are using a named range. The range "status" is specified as: ='C:\Documents and Settings\[user]\Desktop\Projects\[filename.xls]Worksheet1'!$F$12:$Y$236 An example formula is: =COUNTIF(status,"p*") Is this a known issue? Or am I just missing something completely obvious? Cheers Rob -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Issues referencing range between workbooks
Hi Dave,
Thanks heaps for clarifying that. Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a worksheet name from a range | Excel Worksheet Functions | |||
Pivot table range issues from report writer download | Excel Discussion (Misc queries) | |||
Functions referencing unopened workbooks | Excel Worksheet Functions | |||
Formula to Sum a definable range of workbooks | Excel Worksheet Functions | |||
Referencing workbooks | New Users to Excel |