getting autofill to recognise pattern in formulas

  • 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

jo
 
If there's lots of them, this might be worth the effort:

Put something like this in your cell:
="zzz"&"if(conditions!bx"&20+ROW(A1)*2 &
"=0,"""",conditions!$bx"&20+ROW(A1)*2&")"
(all one cell--I put it on two lines to avoid linewrap in the post)

="zzz"&"SI(conditions!bx"&20+ROW(A1)*2 &
"=0;"""";conditions!$bx"&20+ROW(A1)*2&")"

(I tried to translate it to your match your language/settings--Fix it if I
screwed up!)

Drag this down.

You'll see things like:

zzzif(conditions!bx22=0,"",conditions!$bx22)
zzzif(conditions!bx24=0,"",conditions!$bx24)
zzzif(conditions!bx26=0,"",conditions!$bx26)
zzzif(conditions!bx28=0,"",conditions!$bx28)

or
zzzsi(conditions!bx22=0;"";conditions!$bx22)
zzzsi(conditions!bx24=0;"";conditions!$bx24)
zzzsi(conditions!bx26=0;"";conditions!$bx26)
zzzsi(conditions!bx28=0;"";conditions!$bx28)

But they're really formulas. Copy that range and paste|special Values. Now
with that range selected Edit|Replace zzz with =.

Excel will see the equal sign and make it into a formula for you. (If you were
careful (check it twice), it should work ok.)

(Watchout for translation errors!!!)
 
Back
Top