Indirect address

S

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
 
J

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
 
S

stew

Thank you Jacob

Best

Stewart

Jacob Skaria said:
Try

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

If this post helps click Yes
 
S

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
 
J

Jacob Skaria

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

If this post helps click Yes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with indirect 5
ongoing question re hyperlink 4
Indirect Address Problem 1
Locating cell using Hyperlink 10
Address 5
Duplication Macro 0
conditional on change of Month 0
Vlookup or Hlookup 8

Top