Some formulas not auto replicating

nkchri2

New Member
Joined
Mar 1, 2012
Messages
2
I have a excel worksheet that has 6 rows of 12 at the top of the page as a reference table. There are then 3 columns going down the left in which numbers are inserted by the user. These inputs are then run through the various formulas in the columns to the right. The problem is only about half of the formulas are autoreplicating down the page when you enter a new row of the 3 inputs and I can't figure out why some are and some aren't.

  • The columns that aren't replicating are not directly beside each other, they are mixed in with ones that are replicating.
  • I have selected the entire workbook and made sure cell formatting is set to general. I have tried with the set to numbers as well. All the cells formulas result in number data or true/false columns. Some true/false columns are replicating, most are not.
  • The true/false columns that aren't replicating I changed to try and output 1 or 0 and they still did not replicate.
Here is the list of formulas in the workbook that do replicate:

=IF(B50=0,FALSE,ISEVEN(B50))
=IF(B50=0,FALSE,ISODD(B50))
=IF(J50=TRUE,1,0)
=IF(B50=0,0,IF(B50<=18,1,0))
=IF(B50=0,0,IF(B50>=19,1,0))
=COUNTIF($B$1:$M$1,B50)
=COUNTIF($B$2:$M$2,B50)
=COUNTIF($B$3:$M$3,B50)
=COUNTIF($B$4:$M$4,B50)
=COUNTIF($B$5:$M$5,B50)
=COUNTIF($B$6:$M$6,B50)

And formulas that aren't auto replicating:

=IF((C50+C49+C48)=0,TRUE,)
=IF((D56+D55+D54)=0,TRUE,)
=IF((H56+H55+H54)=0,TRUE,)
=IF((K56+K55+K54)=0,TRUE,)
=IF((M56+M55+M54)=0,TRUE,)
=IF((O56+O55+O54)=0,TRUE,)
=IF((Q56+Q55+Q54+Q53+Q52+Q51)=0,TRUE,)
=IF((S56+S55+S54+S53+S52+S51)=0,TRUE,)
=IF((U56+U55+U54+U53+U52+U51)=0,TRUE,)
=IF((W56+W55+W54+W53+W52+W51)=0,TRUE,)
=IF((Y56+Y55+Y54+Y53+Y52+Y51)=0,TRUE,)

The first two of the not working list work directly from the inputs from the user which are numbers. The rest work off the results of formulas in other cells. I have tried changing these to =IF((C50+C49+C48)=0,TRUE,FALSE) or =IF((C50+C49+C48)=0,1,0) and they still won't replicate.
However the formulas DO work and I can drag the formulas down with the cross to fill the next line and they continue down correctly. I just don't know why that bottom list isn't auto replicating so I don't have to drag down the line every time I put in a new line. Again, they are not blocked together either. They don't work in columns E, F, I, L, N, P, R, T, V, X, Z, AB.


I don't know if there is some formula problem preventing those certain columns from replicating or what. Someone in another forum mentioned using a worksheet_change event, but I basically don't know anything about writing VB in excel. If that is something that would be easily written, all it would have to do is if there is a change to any cell in column D, then E:AB need to be copied down from the prior row. For example, a change in D10 would result in E9:AB9 to be copied to E10:AB10.

Thanks in advance for the help!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
I can't find the reference, but I believe Excel looks back five rows and will autofill a formula only if it occurs in three of those five rows.
 
Upvote 0

Forum statistics

Threads
1,223,367
Messages
6,171,671
Members
452,416
Latest member
johnog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top