• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

show as criteria once

dave_yd

dave_yd

New Member
ndooers,

I want to show activity id once each of them, quantity and value must be cummulative from Sr boq data bank.
Please help me to solve this, i attach my File.

Regards
David
 

Attachments

Hi David ,

Something like this ?

F10 : =SUMIF('SR BOQ Data Bank'!$B$3:$B$9,$A10,'SR BOQ Data Bank'!$D$3:$D$9)

G10 : =SUMIF('SR BOQ Data Bank'!$B$3:$B$9,$A10,'SR BOQ Data Bank'!$H$3:$H$9)

Narayan
 
Hi David ,

Try this :

=IFERROR(INDEX('SR BOQ Data Bank'!$B$3:$B$9,SMALL(IF('SR BOQ Data Bank'!$A$3:$A$9=C4,ROW('SR BOQ Data Bank'!$A$3:$A$9)-MIN(ROW('SR BOQ Data Bank'!A3:A9))+1),1)),"")

entered as an array formula , using CTRL SHIFT ENTER.

If you do not have Excel 2007 or later , then you will have to use :

=IF(ISERROR(INDEX('SR BOQ Data Bank'!$B$3:$B$9,SMALL(IF('SR BOQ Data Bank'!$A$3:$A$9=C4,ROW('SR BOQ Data Bank'!$A$3:$A$9)-MIN(ROW('SR BOQ Data Bank'!A3:A9))+1),1))),"",INDEX('SR BOQ Data Bank'!$B$3:$B$9,SMALL(IF('SR BOQ Data Bank'!$A$3:$A$9=C4,ROW('SR BOQ Data Bank'!$A$3:$A$9)-MIN(ROW('SR BOQ Data Bank'!A3:A9))+1),1)))

again entered using CTRL SHIFT ENTER.

Narayan
 
This works to me, if you don`t mind can you please explain how does it work. I am planning to sort them also by activity id.

Thanks for your help and spending time to me.

Rgds
David
 
I am sorry to reply faster, while i do lock at part `-MIN(ROW(`SR BOQ Data Bank`!$A$3:$A$9)+1` the activity id shows many times, could you please help again, and while replace wr/sr number, the activity id POF0101 shown twice.

Regards
 
I need another formula, the link is still not answer my problem, they only count base on wr/sr number, but i need another counting at activity id where different id, please any one help to solve this

Regards
David
 
I don`t want pivot, because i wanna do another excecution with the form. I have 2 formulas which near to my solution but i cannot combine them correctly, see in the attachment,

Please help me.
 

Attachments

Actually sheet `input qs update` is an input form, the formula that created is to be used as limitation of the quantity and item (activity id). So user only update data equal or less than summarize item at another column that i don`t show in the sheet, it will be added later.
Your pivot is what i want to made, but i don`t want to show as pivot.
Plz help.

Regards
 
I am still looking for a formula to solve this,anyone here please help me,

I want the result like pivot, but using formula.

Regards
David
 
Hi David ,

I am uploading your file as it is ; can you please put in the values you would like to see in the cells A10 , C10 , F10 and G10 , and in the following rows , if applicable ?

Please do not put in any formulae ; just enter the desired values , so that it is clear what results the formulae should return.

Narayan
 

Attachments

You must log in or register to reply here.
Back
Top