Excel Formula, HELP!!!

[RompStar]

Ok, I have two seperate Excel documents, let's call them:

RAW
ACTUAL

In each these are many TABS, but I want to concentrate on only one tab
from each Document:

In RAW Doc, the DATA Tab
In ACTUAL Doc, the FY06 Tab

The DATA tab contains many columns filled with accounting data, but I
want to pull a financial $ value out of it and paste that into a Cell
in FY06 Tab, here are the exact details:

In the FY06 Tab, in Cell AV46 is where I want the foluma to go inside
of, and then I will copy it down, but basically...

In FY06 Tab in column G there is a unique accounting value that
identifies the GL code/account name, that is unique to that transaction
only, this same exact unique value also exists in the DATA Tab of the
other document.

Further so you guys undertand my problem, Financial months in the
yearly quarter are identified with numbers in the DATA Sheet in column
H, 1 - 12, where say 11 is May, 12 is June, that's when our quarter
ends here.

In the DATA Sheet in column AB is where the unique value sits that
matches that of column G46 in FY06 tab as mentioned above.

So for example, right now we are in the 11th month in the quater or
June.

So, the formula would go something like this (the formula sits in the
FY'06 Tab, Cell AV46).

So....

Look in column G46 of FY06 Tab (remember the unique value of that
cell), then hop over to the DATA Tab and look into column H for values
that are 11 only.

So when it finds the first value of 11 in column H (whatever row that
would be), then on that same row, look at column AB and if the value
there matched that of G46 from the FY06 Tab, then look at column F in
that same row of the DATA Tab and paste that dollar $ value back to the
formula in the FY06 Tab.

If there are multiple rows that have 11 and the unique value that
matches that of G46 in the FY06 Tab then add them up and post that to
the formula, if one then just post one $.

Does this makes sense? because I don't know how to explain it any
easier, please help!!!!

I am thinking maybe array formula or whatever you guys thinks works the
best....

Thank you ahead of time....

So one more time:

FY06 Tab, look at G46 and remember that Value, then switch to DATA Tab,
and in column H find the first instance of 11, once it finds it on that
same row look at column AB, and if that value matches of that in G46 of
the FY06 Tab, then look at column F (still Data Tab) and grab that $$$
number and paste that back to the formula. Should there be multiple
transactions under that account, add them up and then enter into the
Formula, make sense ?

lol, I hope so...

 

[Scott Collier]

It might be worth looking at creating a custom formula in the visual basic
editor.

FY06
G46=Reference number = x

Data
Column F Column H Column AB
------------------------------------
1 11 x
2 10 x
3 11 y
4 11 x

According to your explanation .. the answer using the example above would be
the sum of 1 + 4 = 5

Conditions that have to be met.
Cell in H has to =11
Cell in AB has to = x (which is the same as G46)

Sum the value in Column F if the above 2 conditions have been met.

Hope this helps

Scott

 

[RompStar]

acctually I solved with help of someone else, but I want to show you
guys the solution incase there are other people with the same problem,
to help them...

On the DATA sheet, column H is the month.
On the DATA sheet, column K is a unique number (720001)
On the DATA sheet, column N is a unique number (1111110)
On the DATA sheet, column F is the dollar values you want to sum.
On the FY06 sheet, cell BE44 is the month number you are checking for:
11 in this example.
On the FY06 sheet, cell G46 is the unique number you are checking for:
720001 in this example.
On the FY06 sheet, cell E46 is the unique number you are checking for:
1111110 in this example.

With all of that, you get the following in cell BD46:

=SUMPRODUCT(--([RAW.xls]DATA!$H$1:$H$25=11),--([RAW.xls]DATA!$K$1:$K$25=$G$46),--([RAW.xls]DATA!$N$1:$N$25=$E$46)*([RAW.xls]DATA!$F$1:$F$25))


and no array was needed.... :- )

see you later.