Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array, multiple sheets
Hi all,
I'm looking at doing some "special" SUM & IF, and I know the formula I want if it all had been in one sheet. But when trying to go across worksheets I run into problems. If on one sheet I would check column L for a criteria (=1 in example below, or a cell ref. in real life), if criteria is right I will add column H * column I and summarize the total. This is the formula I would use: {=SUM(IF(L2:L10=1,H2:H10*I2:I10,0))} Now I want to combine this with the possibility of making formulas like these: =SUM(Sheet1:Sheet10!B3) So I want to check cell B1 on Sheet1 to 10 is equal to B1 on the current sheet, if so multiply B2 and B3 on that sheet and in the end add up all the ones that meet the "IF" requirement. I was thinking of a formula looking something like this: {=SUM(IF(Sheet1:Sheet10!B1=B1,Sheet1:Sheet10!B2*Sh eet1:Sheet10!B3,0))} This gives #REF! I've tried to create a couple of other array formulas across worksheets, and I can't get any of them to work. Is it not possible to use arrays { } (ctrl + shift + enter) across worksheets? Or is it just something I'm missing that must be included when working across worksheets that I can add and everything is working? If this is not possible through an array formula, can anyone start pointing me in the right direction for creating a custom user function in VBA for this? I'd be happy for any help on this since it's been bugging me for a couple of days now. Ronny |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array, multiple sheets
Array formulas do not work across sheets in the fashion you envision.
You can have an array formula for each sheet and sum the results. -- Regards, Tom Ogilvy "Ronny" wrote in message oups.com... Hi all, I'm looking at doing some "special" SUM & IF, and I know the formula I want if it all had been in one sheet. But when trying to go across worksheets I run into problems. If on one sheet I would check column L for a criteria (=1 in example below, or a cell ref. in real life), if criteria is right I will add column H * column I and summarize the total. This is the formula I would use: {=SUM(IF(L2:L10=1,H2:H10*I2:I10,0))} Now I want to combine this with the possibility of making formulas like these: =SUM(Sheet1:Sheet10!B3) So I want to check cell B1 on Sheet1 to 10 is equal to B1 on the current sheet, if so multiply B2 and B3 on that sheet and in the end add up all the ones that meet the "IF" requirement. I was thinking of a formula looking something like this: {=SUM(IF(Sheet1:Sheet10!B1=B1,Sheet1:Sheet10!B2*Sh eet1:Sheet10!B3,0))} This gives #REF! I've tried to create a couple of other array formulas across worksheets, and I can't get any of them to work. Is it not possible to use arrays { } (ctrl + shift + enter) across worksheets? Or is it just something I'm missing that must be included when working across worksheets that I can add and everything is working? If this is not possible through an array formula, can anyone start pointing me in the right direction for creating a custom user function in VBA for this? I'd be happy for any help on this since it's been bugging me for a couple of days now. Ronny |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array, multiple sheets
Thank you for your quick reply.
That's what I was afraid of. Well, there is only one cell * one cell on each sheet, so an array would not be needed there. But that would mean I would do =IF(Sheet1!B1=B1,Sheet1!B2*Sheet1!B3,0)+ IF(Sheet2!B1=B1,Sheet2!B2*Sheet2!B3,0)+ IF(Sheet3!B1=B1,Sheet3!B2*Sheet3!B3,0)+... etc. and I would very quickly run out of the number of IFs I can use. And it has no flexibility as to insert more sheets etc. Do you know of any other way? Or is it possible to create something in VBA? I'm not really sure how VBA deals with ranges across worksheets. Ronny |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array, multiple sheets
My contention is that VBA does not support 3D arguments in UDF's. However,
someone may have better ideas than I do. In the past I have posted a solution that parses the argument as a string and resolves it, so if no one comes up with something better, you can use that approach as shown at the URL:. http://tinyurl.com/c6oo9 -- Regards, Tom Ogilvy "Ronny" wrote in message ups.com... Thank you for your quick reply. That's what I was afraid of. Well, there is only one cell * one cell on each sheet, so an array would not be needed there. But that would mean I would do =IF(Sheet1!B1=B1,Sheet1!B2*Sheet1!B3,0)+ IF(Sheet2!B1=B1,Sheet2!B2*Sheet2!B3,0)+ IF(Sheet3!B1=B1,Sheet3!B2*Sheet3!B3,0)+... etc. and I would very quickly run out of the number of IFs I can use. And it has no flexibility as to insert more sheets etc. Do you know of any other way? Or is it possible to create something in VBA? I'm not really sure how VBA deals with ranges across worksheets. Ronny |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array: Counting multiple values within array | Excel Worksheet Functions | |||
combining cells and array from different sheets into an array to pass to IRR() | Excel Discussion (Misc queries) | |||
Printing multiple sheets in an array | Excel Programming | |||
Printing multiple sheets in an array | Excel Programming | |||
selecting multiple sheets by use of an array? | Excel Programming |