Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula : Problems with sharing workbook
I currently use an array formula to capture data from a specific
location. The problem is that the array formula prevents the workbook being shared by more than one person. Please can you advice on what to do to the formula below i.e. to use non array instead tol capture the same data. Below is an illustration of the type of task the formula id currently used for; SOURCE DATA Column A B C 1 2 KMAA 5101 450 3 KMAA 5101 320 4 KMSA 4102 987 5 KMSD 5454 189 RESULT AREA (this is where the array formula is) Column A B C 1 KMAA 2 3 5101 {=SUM('[IF(Book1.xls]SOURCE DATA'!A2:A5=$B $1,IF(Book1.xls]SOURCE DATA'!B2:B5=$A3,Book1.xls]SOURCE DATA'! C2:C5)))} 4 5 I have tried everything to change the above to non array so that that the workbook could be shared with no success. Any advice would be much appreciated. Many thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula : Problems with sharing workbook
First, it's better to post the working formula--just copy from the formula bar
and paste into the message. It makes it easier for people to help. Second, sometimes you can use =sumproduct() for =sum(if())'s: =SUMPRODUCT(--('[book1.xls]Source Data'!A2:A5=$B$1), --('[book1.xls]Source Data'!B2:B5=$A3), ('[book1.xls]Source Data'!C2:C5)) 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 Ola2B wrote: I currently use an array formula to capture data from a specific location. The problem is that the array formula prevents the workbook being shared by more than one person. Please can you advice on what to do to the formula below i.e. to use non array instead tol capture the same data. Below is an illustration of the type of task the formula id currently used for; SOURCE DATA Column A B C 1 2 KMAA 5101 450 3 KMAA 5101 320 4 KMSA 4102 987 5 KMSD 5454 189 RESULT AREA (this is where the array formula is) Column A B C 1 KMAA 2 3 5101 {=SUM('[IF(Book1.xls]SOURCE DATA'!A2:A5=$B $1,IF(Book1.xls]SOURCE DATA'!B2:B5=$A3,Book1.xls]SOURCE DATA'! C2:C5)))} 4 5 I have tried everything to change the above to non array so that that the workbook could be shared with no success. Any advice would be much appreciated. Many thanks. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sharing Problems- users keep reappearing even though logged out. | Excel Discussion (Misc queries) | |||
Array Formula Limits (around 2800 or so) ? Lotus DSUM has no problems with it ! | Excel Discussion (Misc queries) | |||
Sharing problems | Excel Discussion (Misc queries) | |||
problems with Sharing excel files | Excel Discussion (Misc queries) | |||
File Sharing Problems - Error Message | Excel Discussion (Misc queries) |