Indirect address

[stew]

Hi all

In Row 490 Column H I have the following

=INDIRECT("'Road Cash
Accounting'!"&ADDRESS(INT(ROW()/49),(INT(COLUMN()+1)))),0)

This displays the value in 'Road Cash Accounting'$I$10

How can I adapt the above formula in cell Row 539 Column H ( 49 rows on.
This repeats every 49 rows) to pick out 'Road Cash Accounting'$I$52 ( 42
lines on. This repeats every 42 lines)

Hope someone can help

Best

Stew

 

[Jacob Skaria]

Try

(all in one line)
=INDIRECT("'Road Cash
Accounting'!"&ADDRESS(10+(MOD(ROW(),490)/49)*42,COLUMN()+1))

If this post helps click Yes

 

[stew]

Thank you Jacob

Best

Stewart

Try

(all in one line)
=INDIRECT("'Road Cash
Accounting'!"&ADDRESS(10+(MOD(ROW(),490)/49)*42,COLUMN()+1))

If this post helps click Yes

 

[stew]

Hi Jacob

Small Problem with this Formula

When we reach row 960 the indirect instruction starts at 'Road Cash
Accounting'$I$10 again when it should carry on to $I$430.This applies through
to the end of the Sheet

Can you help

 

[Jacob Skaria]

(in one line...)
=INDIRECT("'Road Cash
Accounting'!"&ADDRESS(10+((ROW()-441)/49-1)*42,COLUMN()+1))

If this post helps click Yes