I have several worksheets - i need to reference from one sheet 3 different
values
IE text, text, then add the values.
EG range A1:A1000 looks for a value =HPI835
Range B2:B2000 looks for a value on sheet to which must be =A2
then it must add all the values found together an put it onto sheet 3 in BD34
if this does not make sence then have a look at this formula i used which
works well for a few cells but not for a range of cell it as an array
formula
{=SUM(IF((Report!$B$2:$B$20000="ESM005")*(Report!$ F$2:$F$20000='Kit
Planes'!$A4),Report!$C$2:$C$20000))}
please can somebody help me to solve my problem..
many thanks

Not getting what the problem is. Does your formula work? Does it need adding
to, if so in what way?

--
HTH

Bob Phillips

"CHrisD" wrote in message
...
I have several worksheets - i need to reference from one sheet 3 different
values
IE text, text, then add the values.
EG range A1:A1000 looks for a value =HPI835
Range B2:B2000 looks for a value on sheet to which must be =A2
then it must add all the values found together an put it onto sheet 3 in
BD34
if this does not make sence then have a look at this formula i used which
works well for a few cells but not for a range of cell it as an array
formula
{=SUM(IF((Report!$B$2:$B$20000="ESM005")*(Report!$ F$2:$F$20000='Kit
Planes'!$A4),Report!$C$2:$C$20000))}
please can somebody help me to solve my problem..
many thanks

hi there Bob
I have got a range of worksheets, rows = customer colums = parts
i work of 3 original sheet ie customer list, reports, inventory.
all the customers within the rows in colum A is referenced to the customer
sheet.
so if i change the customer sheet all the other sheets change aswell.
now i get a report that is drawn from alcamex and i export it to my excel
sheet.
I used to imput all the data manualy into each respective sheet and partno:
i would like this to be done automatically.
so what i need to do is to get a formula that will look into the report
sheet get customer info (Name)- Report!$F$2:$F$20000='Kit Planes'!$A4 - and
then also the product he bought (part no) - Report!$B$2:$B$20000="ESM005" -
(i put the part no in for that field) and qty bought - Report!$C$2:$C$20000 -
and put it onto the relvenat sheet(this complete formula is put into each
cell for each customerand product - (i know that i would have to program
each field) as you see i was trying to use an array formula - it works for a
few cells - but not for the whole sheet - makes my computer hang.
the reason for me programing each cell which would reference to the customer
list aswell as the partno: is as and when new customers come onboard and
other fall by the way side all i do is update the customer list and the rest
of the sheets are updated automatically aswell as the amounts they have
bought. that is why i use Report!$F$2:$F$20000='Kit Planes'!$A4 (this
references to the customer name on that sheet - which inturn is reference to
the customer sheet
If you would need me to send you the workbook to see what i mean; please
advice me and i will send it to you

once again
many thanks for you helping me
CHrisD


"Bob Phillips" wrote:

Not getting what the problem is. Does your formula work? Does it need adding
to, if so in what way?

--
HTH

Bob Phillips

"CHrisD" wrote in message
...
I have several worksheets - i need to reference from one sheet 3 different
values
IE text, text, then add the values.
EG range A1:A1000 looks for a value =HPI835
Range B2:B2000 looks for a value on sheet to which must be =A2
then it must add all the values found together an put it onto sheet 3 in
BD34
if this does not make sence then have a look at this formula i used which
works well for a few cells but not for a range of cell it as an array
formula
{=SUM(IF((Report!$B$2:$B$20000="ESM005")*(Report!$ F$2:$F$20000='Kit
Planes'!$A4),Report!$C$2:$C$20000))}
please can somebody help me to solve my problem..
many thanks