Please explain this formula

[stew]

Hi All

I am totally out my depth but I need to Learn.In b11 on a worksheet is the
following.
=INDIRECT("'date details'!"&ADDRESS(3,(INT(ROW()/80)+3))) returns the
following results
the date that is in cell c3 on worksheet "date details "appears b11 when the
above formula is placed in it
the date that is in cell d3 on worksheet "date details" appears B91when the
above formula is placed in it
the date that is in cell E3 on worksheet "date details" appears B171when the
above formula is placed in it

and on and on whenever you pace that formula

How is the 80 row sequence worked out and how is it relative to the intial.
cell of B11

Remember I'm new to this

Best
Stew

 

[Bob Phillips]

In B11

ROW() =11
INT(ROW()/80)+3) = 3
ADDRESS(3,INT(ROW()/80)+3)) = C3

In B91

ROW() =91
INT(ROW()/80)+3) = 4
ADDRESS(3,INT(ROW()/80)+3)) = D3

etc.

 

[stew]

So" int "rounds it down to the nearest whole number.
Next question is there a re calculation that allows you to achieve that 1
increase
ie
if my formula is in Row 475 how can you work out to get
int(row()/? to increase on a 49 row increase working on achieving Plus 1
every time

Thanks

stewart

 

[Bob Phillips]

You mean that row 475 refers to a row 1, 478 refers to a row 50? Is that
it? What column?

 

[stew]

Dear Bob
Thanks for your time
B475 will contain the formula
=INDIRECT("'date details'!"&ADDRESS(3,(INT(ROW()/?)+?)))
In B524 THE FORMULA WOULD BE REPEATED TO GIVE THE INCREASE IN C3 TO D3

My question is INT(ROW() in B475 = 475
the next location is B524
INT(ROW() IN b524= 524

is there a set calculation to work out the divisor that would give the +1 to
allow me to move along the C3,D3,E3,F3 increase every time when dividing
these Numbers.
Sorry if I am not expaining this very well

 

[David Biddulph]

You don't have INT(ROW()), you have INT(ROW()/80)
Look at where the closing parenthesis of the INT() function is.
If you want to step one column for every 49 rows, rather than one column for
every 80 rows, then change INT(ROW()/80) to INT(ROW()/49)

As for the second question mark in your formula, if you want the formula in
B475 to point to the third column (i.e. column C), then work out what
INT(475/49) is, then see what you need to add to it (or in this case
subtract) to get the second argument of your ADDRESS function to be 3 in
order to point to column C.

If you are struggling to work out what your formula is doing, you can always
break it into manageable chunks so that you can see what it's doing. If you
can't work out where the INDIRECT function is pointing, you can either just
use =("'date details'!"&ADDRESS(3,(INT(ROW()/80)+3))) to see the address as
a text string, or =INT(ROW()/80)+3 to see the column number. Then if you
change your 80 to 49, or change your 3 to -6, you can see what the effect
is.

 

[stew]

Dear David , and Bob

Thank you. You have taught and old dog a new trick. I look forward to
learning More. It made it so much easier when I was able to see where the
Formula took me as a text string.

Thanks Again

Stew