Array Formula (or VB)

[sirrahmichelle]

The Problem:
I have another complicated problem, for me at least.

I have a several tabbed workbook with the following tabs that are involved with what I need:
"Material ODC and Travel"
"Plan"

I need either an Array formula or a macro that will pull in dollar amounts from the "Plan" tab to the "Material ODC and Travel" tab.

Some background on "Material ODC and Travel" tab:
The "Material ODC and Travel" tab is set up into 3 categories. Material, ODC and Travel. Each Category has 7 possible delivery orders. Each Delivery order has a Planned and an Actual amount. Then add another column for each Period, (which is another term for the month of the year). I need to fill in the blanks with the formula (or VB code).

For a visual, here is an example of the first Category, the subsequent categories of ODC and Travel follow on rows below Material in the same structure.

Please note that Cells A6:A19 are actually merged.

Category Del Ord. Period 1 Period 2
(A6:A19)(B6:B19)(C6:C19)(D6:D19)
Material DO 1 (P) _______ _______
Material DO 1 (A) _______ _______
Material DO 2 (P) _______ _______
Material DO 2 (A) _______ _______
Material DO 3 (P) _______ _______
Material DO 3 (A) _______ _______
Material DO 4 (P) _______ _______
Material DO 4 (A) _______ _______
Material DO 5 (P) _______ _______
Material DO 5 (A) _______ _______
Material DO 6 (P) _______ _______
Material DO 6 (A) _______ _______
Material DO 7 (P) _______ _______
Material DO 7 (A) _______ _______

Some background on "Plan" tab:

This tab is an itemized list of Planned Dollar amounts. The column headers are titled respectively, starting with Column A and ending with Column N (Column I is left blank for future spacing/enhancements that will be required down the road):

Year Period D.O. ODC Item Cost Qty Total Cost (column I) DO 1 DO 2 DO 3 DO 4 DO 5

Year(A:A): Years will always be from 2009 to 2015 (only one can be selected from drop down list)
Period(BLB): Periods will always be from Period 1-12(only one can be selected from drop down list)
DO(C:C): DO will always be Delivery order 1 - 5 (only one can be selected from drop down list)
ODC(D:D): ODC column will be Material, Travel or ODC (only one can be selected from drop down list)
Item, Cost and Qty(F:F thru H:H): will change as necessary.
DO 1 - DO 5(J:J thru N:N): The columns sum the dollar amounts pending the selected Delivery order in (C:C)"D.O.".

The objective:
Lets see if I can explain this clearly... I need the Planned (noted with a "(P)" Project amounts to be picked up from the "Plan" tab to the "Material ODC and Travel" tab into the in their respective Material Category, Project and Period cells.

Do you think this is possible?

 

[sirrahmichelle]

P.S. I was given this array last week for a similar (simpler) task, but not sure how to imbed the 4 if's if that's what is even needed to be done.

{=SUM(IF(People!$B$6:$B$1041=Projects!$B139,(People!$H$6:$H$1041*People!$DI$6:$DI$1041)))}

 

[Joe Was]

Sirrah, Without a complex macro, I do not think merged cells can be worked with the way you want easily?

Now, if you had a column for the "P & A" codes and an additional column for the values, it would be easy to put copies of these values into other sheets. If this is what you want to do?
I have read your post a few times and just can't wrap my mind around what it is you actually want to do?

 

[sirrahmichelle]

I need the info from the Plan tab that is listed to appear in the appropriate spot on the Materials, ODC and Travel tab. I need a formula that intuitively looks for the Year, the Category, the Period and the delivery order and sum up all planned transactions on the main data table. This help?

 

[sirrahmichelle]

Let me get some coffee and then I try this again.

 

[sirrahmichelle]

I'm not going to waste any more of your time, actually. A good friend sent me this via email. The tab names are different for simplicity.

{=SUM((Plan!$A$4:$A$31=Data!$S$5)*(Plan!$B$4:$B$31=Data!G$5)*(CONCATENATE(Plan!$C$4:$C$31," (P)")=Data!$B6)*(Plan!$D$4:$D$31=Data!$A$6)*(Plan!$H$4:$H$31))}

Thank you for trying anyway, Joe.

 

[DonkeyOte]

Although I doubt there would be a huge difference in performance you might want to convert to Sumproduct from perspective of robustness (ie if edited no requirement to reset array) ... it's also argued that coercion of boolean using double unary is slightly quicker method

=SUMPRODUCT(--(Plan!$A$4:$A$31=Data!$S$5),--(Plan!$B$4:$B$31=Data!G$5),--(CONCATENATE(Plan!$C$4:$C$31," (P)")=Data!$B6),--(Plan!$D$4:$D$31=Data!$A$6),Plan!$H$4:$H$31)

though others have argued that given single coercion is quicker still it's better to use that and reverse the sign of result where required to ensure correct result, eg:

=SUMPRODUCT(-(Plan!$A$4:$A$31=Data!$S$5),-(Plan!$B$4:$B$31=Data!G$5),-(CONCATENATE(Plan!$C$4:$C$31," (P)")=Data!$B6),-(Plan!$D$4:$D$31=Data!$A$6),Plan!$H$4:$H$31)

in the above example no reversal of result is required in the above given we have 4 boolean tests -- were the number of tests odd we would need to reverse, ie =-SUMPRODUCT(...)

 

[sirrahmichelle]

I guess this is way above my head.

What does a boolean test? How did it happen 4 times? Care to break it down to a noobie?

 

[DonkeyOte]

Sorry

By 4 Boolean (True/False) Results I mean you have 4 conditional tests in your formula, namely:

Plan!A4:A31=Data!S5 --> result True/False
Plan!B4:B31=Data!G5 --> result True/False
Concat(Plan...)=Data!B6 --> result True/False
Plan!D4:D31=Data!A6 --> result True/False

By using a unary operator we can Coerce the Boolean (True/False) to it's integer equivalent, where by default TRUE = 1 and FALSE = 0
(we could do the same by multiplication (eg 1*TRUE = 1) but unary is regarded as quickest method -- your friends SUM array was for ex. coercing via multiplication)

So if we use:

--(Boolean)

we will get 1 returned for TRUE (--1 = 1) and 0 for FALSE given 2 negatives = positive.

If on the other hand we use a single unary operator:

-(Boolean)

we will get -1 returned for TRUE and 0 for FALSE

In the Sumproduct the results of the coerced boolean values are multiplied as a Product by row and then those Products summed.

So if like your formula we assume there are 4 Boolean results and then one set of numerical values to sum (Plan!H4:H31) you could get a result array for a particular row in your range (4:31) along the lines of the below:

{TRUE,TRUE,TRUE,TRUE,25}
note this is showing ignoring impacts of unary operators

If you were coercing the Boolean results using the double unary operator the above would equate to:

{1,1,1,1,25} -> result = 25

If you were coercing the Boolean results using the single unary operator the sample array would equate to:

{-1,-1,-1,-1,25} -> result = 25

Now let's assume we were to dispense with one of those conditional arguments so we have 3 boolean results and one numerical value to sum, eg:

{TRUE,TRUE,TRUE,25}

Using Double unary operator coercion we would get:

{1,1,1,25} -> result = 25

However with single unary operator we would get:

{-1,-1,-1,25} -> result = -25

So because the count of our coerced booleans is odd - if all results are TRUE we will end up with a multiplier of -1 rather than 1 and as such our result is the incorrect SIGN ... to correct for this we would have to precede our Sumproduct formula with a minus sign so as to reverse the result such that it returns to 25 as it should be.

On this basis double unary method is regarded as safer as the count of conditional arguments is irrelevant but the single unary operator is regarded as quicker as it takes less time to do one coercion (-) than it does two (--).

One of the real guru's on this stuff (not going to name names) can outline this better than I -- if you do some searching on the subject of "Double Unary Operator" on this Forum you should find some interesting reads (if you find the above interesting of course )

(FWIW - on an aside note in VBA True = -1)