Help with indirect
 

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

Help with indirect
 

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


"stew" wrote in message
...
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

Help with indirect
 

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
---
"stew" wrote:
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

Help with indirect
 

My interp was
In A722, OP wanted it as: ='Road Cash Accounting'!A114
In A754, as: ='Road Cash Accounting'!A217
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Bernie Deitrick" wrote:
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.

Help with indirect
 

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


"Max" wrote in message
...
My interp was
In A722, OP wanted it as: ='Road Cash Accounting'!A114
In A754, as: ='Road Cash Accounting'!A217
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Bernie Deitrick" wrote:
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.

Help with indirect
 

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

"Max" wrote:

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
---
"stew" wrote:
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