recognising patterns in formulas - autofill

  • Thread starter Thread starter Jo Wood
  • Start date Start date
J

Jo Wood

SO I have a column starting at R23

=SI(Conditions!$BX22=0;"";Conditions!$BX22)
=SI(Conditions!$BX24=0;"";Conditions!$BX24)
=SI(Conditions!$BX26=0;"";Conditions!$BX26)
=SI(Conditions!$BX28=0;"";Conditions!$BX28)

You or I can see that the next row down should be the
above formula with BX30 replacing BX28

While excel recognises the pattern that the row value
reference be increased by 2 each time, it doesn't
recognise which value to start with

if I select the last two rows of the column and drag down
for three i get

=SI(Conditions!$BX22=0;"";Conditions!$BX22)
=SI(Conditions!$BX24=0;"";Conditions!$BX24)
=SI(Conditions!$BX26=0;"";Conditions!$BX26)
=SI(Conditions!$BX28=0;"";Conditions!$BX28)
=SI(Conditions!$BX28=0;"";Conditions!$BX28)
=SI(Conditions!$BX30=0;"";Conditions!$BX30)
=SI(Conditions!$BX30=0;"";Conditions!$BX30)

If I select the whole of the column I get

=SI(Conditions!$BX22=0;"";Conditions!$BX22)
=SI(Conditions!$BX24=0;"";Conditions!$BX24)
=SI(Conditions!$BX26=0;"";Conditions!$BX26)
=SI(Conditions!$BX28=0;"";Conditions!$BX28)
=SI(Conditions!$BX26=0;"";Conditions!$BX26)
=SI(Conditions!$BX28=0;"";Conditions!$BX28)
=SI(Conditions!$BX30=0;"";Conditions!$BX30)

How do I make it do the seemingly obvious????

PS as you may have guessed from the formula this is a
french version of excel, which is serving to confuse me
further.

cheers in advance
 
Hi Jo,

I don't know the French equivalents of OFFSET and ROW, but you would need to
use them to calculate the cells used. e.g. in English, your formula would
be:

=IF(OFFSET(Conditions!$B$22,(ROW()-ROW(R$23))*2,0)=0,"",OFFSET(Conditions!$B
$22,(ROW()-ROW(R$23))*2,0))

Steve D.
 
See one alternative at your first post.

Jo said:
SO I have a column starting at R23

=SI(Conditions!$BX22=0;"";Conditions!$BX22)
=SI(Conditions!$BX24=0;"";Conditions!$BX24)
=SI(Conditions!$BX26=0;"";Conditions!$BX26)
=SI(Conditions!$BX28=0;"";Conditions!$BX28)

You or I can see that the next row down should be the
above formula with BX30 replacing BX28

While excel recognises the pattern that the row value
reference be increased by 2 each time, it doesn't
recognise which value to start with

if I select the last two rows of the column and drag down
for three i get

=SI(Conditions!$BX22=0;"";Conditions!$BX22)
=SI(Conditions!$BX24=0;"";Conditions!$BX24)
=SI(Conditions!$BX26=0;"";Conditions!$BX26)
=SI(Conditions!$BX28=0;"";Conditions!$BX28)
=SI(Conditions!$BX28=0;"";Conditions!$BX28)
=SI(Conditions!$BX30=0;"";Conditions!$BX30)
=SI(Conditions!$BX30=0;"";Conditions!$BX30)

If I select the whole of the column I get

=SI(Conditions!$BX22=0;"";Conditions!$BX22)
=SI(Conditions!$BX24=0;"";Conditions!$BX24)
=SI(Conditions!$BX26=0;"";Conditions!$BX26)
=SI(Conditions!$BX28=0;"";Conditions!$BX28)
=SI(Conditions!$BX26=0;"";Conditions!$BX26)
=SI(Conditions!$BX28=0;"";Conditions!$BX28)
=SI(Conditions!$BX30=0;"";Conditions!$BX30)

How do I make it do the seemingly obvious????

PS as you may have guessed from the formula this is a
french version of excel, which is serving to confuse me
further.

cheers in advance
Click to expand...
 
Back
Top