Help with indirect

S

stew

Hi all

I am using this Formula in worksheet sheet 1 A690
=INDIRECT("'Road Cash Accounting'!"&ADDRESS(ROW()/6-104,(COLUMN()))) to get
to A11 in Road cash accounting.
I am having difficulty adapting to make it repetitive in the following manner

In Worksheet sheet 1 A722 ,A690+32 Rows
has to display
In road cash accounting A114, A11+103 Rows
and again
In worksheet sheet 1 A754 , A722+32 Rows
Has To Display
In Road Cash Accounting A217,A114+103 Rows

And so on

Hope you can help

Stew
 
B

Bernie Deitrick

Stew,

Don't use INDIRECT.

In A690, use

='Road Cash Accounting'!A11

and copy that to A722. Excel will update the formula to

='Road Cash Accounting'!A43

etc.

HTH,
Bernie
MS Excel MVP
 
M

Max

One way

Use instead in A690:
=INDIRECT("'Road Cash Accounting'!"&"A"&(INT((ROWS($1:1)-1)/32)+1)*103-103+11)

Then you could just copy A690, paste into A722 & A754, etc to return
required results

Expression above is intentionally left w/o arithmetic simplification for the
tail part of it for easier clarity on how the 103 interval comes into play.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
 
B

Bernie Deitrick

Max,

In his description, the OP was bouncing back and forth between sheets:

In A690 .... to get A11 in Road cash accounting.
In A722 - A690+32 Rows
In A754 - A722+32 Rows

HTH,
Bernie
MS Excel MVP
 
S

stew

Thank you both for your Help

I ended up with

=INDIRECT("'Road Cash Accounting'!"&ADDRESS(103*ROW()/32-2209,(COLUMN())))

It seems to work at the moment?

Stewart
 

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


Top