• 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.

continuing formula hundreds of times

T

traceym

New Member
Hi,

i am working on a database spreadsheet and would like help with formulas.

I have a set of formulas in a box above and what I'd like to do is when I insert a new row I'd like the formulas above it to continue in the rows below.

I'd like to continue inserting rows below into their hundreds.

I have done 3 rows manually & it looks like this>>>

=IF(A16=$K$4,IF(AT16="PH","1","0"),0)+IF(A17=$K$4,IF(AT17="PH","1","0"),0)+IF(A18=$K$4,IF(AT18="PH","1","0"),0)

I am new to macros but I have attempted a macros and it just repeats the last section of the formula.
 
Tracey

If you select the cell/s with the formulas

Then double click the small black square at the bottom right corner

Excel wll coy the formulas down to the bottom of your data
 
Thank you for your help, but that is not the solution that I need.

You see, I have 4 criteria that all have an if formula one under another...

=IF(A16=$K$4,IF(AT16="PH","1","0"),0)+IF(A17=$K$4,IF(AT17="PH","1","0"),0)+IF(A18=$K$4,IF(AT18="PH","1","0"),0)

=IF(A16=$K$4,IF(AT16="WIE","1","0"),0)+IF(A17=$K$4,IF(AT17="WIE","1","0"),0)+IF(A18=$K$4,IF(AT18="WIE","1","0"),0)

=IF(A16=$K$4,IF(AT16="EE","1","0"),0)+IF(A17=$K$4,IF(AT17="EE","1","0"),0)+IF(A18=$K$4,IF(AT18="EE","1","0"),0)

=IF(A16=$K$4,IF(AT16="RES","1","0"),0)+IF(A17=$K$4,IF(AT17="RES","1","0"),0)+IF(A18=$K$4,IF(AT18="RES","1","0"),0)


My initial row has data validation dropboxes in it where my sales person will select PH, WIE, EE, or RES from them.

I need to insert more rows exactly the same but need the formulas to add in the row. e.g. =IF(A16=$K$4,IF(AT16="RES","1","0"),0)+IF(A17=$K$4,IF(AT17="RES","1","0"),0)+IF(A18=$K$4,IF(AT18="RES","1","0"),0)+IF(A19=$K$4,IF(AT19="RES","1","0"),0)


I would like to add in at least 500 rows of the above.
 
Tracey


can you upload a sample file for us to review.

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Tracey


I7: =COUNTIFS($A$16:$A$36,$K$4,$AT$16:$AT$36,"PH")

I8: =COUNTIFS($A$16:$A$36,$K$4,$AT$16:$AT$36,"WIE")

I9: =COUNTIFS($A$16:$A$36,$K$4,$AT$16:$AT$36,"EE")

I10: =COUNTIFS($A$16:$A$36,$K$4,$AT$16:$AT$36,"RES")
 
That is what the above formula do?


The above formula says Count the Rows where the Enquiry Date = Reporting Date and the Enquiry Type = "PH" etc
 
Similarly in U7:
Code: 
=COUNTIFS($A$16:$A$36,$K$4,$AY$16:$AY$36,"WIT")
 
You must log in or register to reply here.
Back
Top