File Attached : Resending it again as File size was big

[Rajkumar]

Hello all,

I am attaching a file,contains 3 sheets Master,Report 7 Data resp.

I need the Data sheet to be filled up based on the Report sheet.

I am totally confused on how to go about it.

Note : This file is to be used everyday & based on the report sheet
,the data sheet should change automatically.

Pls help.

Rajkumar

Attachment filename: file.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=409823

 

[Frank Kabel]

Hi Rajkumar

i took a look at your spreadsheet and I'm still not so sure what you
want to achieve, but lets give it a try. In your Data sheet / Cell B3 i
assume that you want to porcess all Report Data that have the status
'JNS', Tech-Code '21', Ageing '0-2' days. Return value would be the
number of occurences (assumption)

For this you could use the following function in B3
=SUMPRODUCT((Report!$C$10:$C$100=$A3)*(Report!$E$10:$E$100=$A$1)*(Repor
t!$I$10:$I$100>=0)*(Report!$I$10:$I$100<=2))
copy this down. For the second column (C3) use the following
=SUMPRODUCT((Report!$C$10:$C$100=$A3)*(Report!$E$10:$E$100=$A$1)*(Repor
t!$I$10:$I$100>2))
also copy down

You can use these formulas accordingly for all othe status types. just
change the $A$1 part and the date checking part
If you have further questions i can send you your Excel sheet with
formulas inserted

HTH
Frank

 

[Rajkumar]

Dear Frank,

Absolutely great,wonderful,marvellous.You are genius.

I am really sorry for not ellaborating my msg.But you assumed it to
perfection.

Can you pls fill up the rest of the formulae part in that file & also
can you pls explain(if possible) how did you do it.I am unable to
understand the formaule.i can learn something new today from you.

Thanks once again.
Rajkumar

 

[Rajkumar]

I completed the file myself.

Thanks once again.

Rajkumar