Cell ref is it possibile to calculate / change this in a formula

[oliverkat]

Any SUPER USERS uot there?

I am trying to construct a formula where I need to copy it down 6500+ rows
but I need one of the cell refs to be calculated in sted of having its row
number to raise by one for each row copied.

sample,
Normel coping:
=IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25)
=IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C26)


What I am trying to get:

=IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25)
=IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C16)

For every line the formula is coppied down the C cell ref should show the
row value less 11 row numbers.

Any one knows if this can be done?

 

[Jacob Skaria]

If it is 11 rows less then the refernce should be C14 instead of C16....Try
the below formula and copy down as required

=IF(AND(F25<=201012,F25<>$F$1),B25,
INDIRECT("'MASTER tot'!C" & 25 - (ROW(A1)-1)*11))

25 is the starting range
11 is the difference...(change to suit your requirement)

 

[Pete_UK]

But, if you start with C25 and reduce that by 11 it will give you C14
not C16. Also, you will have negative rows the next few times you copy
it, so this will give an error, and you certainly won't be able to
copy it down 6500 rows.

You would normally use the INDIRECT function for what you want to do,
but you'll need to sort out the problems above before I can suggest a
formula for you.

Hope this helps.

Pete

 

[Mike H]

Hi,

What happens when the formula is dragged and reduces by 11 each row is shown
below, so you can't drag it down 6500+ rows, so what do you really want

=IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25)
=IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C16)
=IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C5)
=IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C-6)

Mike