Indirect address
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 |
Indirect address
Try
(all in one line) =INDIRECT("'Road Cash Accounting'!"&ADDRESS(10+(MOD(ROW(),490)/49)*42,COLUMN()+1)) If this post helps click Yes --------------- Jacob Skaria "stew" wrote: 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 |
Indirect address
Thank you Jacob
Best Stewart "Jacob Skaria" wrote: Try (all in one line) =INDIRECT("'Road Cash Accounting'!"&ADDRESS(10+(MOD(ROW(),490)/49)*42,COLUMN()+1)) If this post helps click Yes --------------- Jacob Skaria "stew" wrote: 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 |
Indirect address
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" wrote: Try (all in one line) =INDIRECT("'Road Cash Accounting'!"&ADDRESS(10+(MOD(ROW(),490)/49)*42,COLUMN()+1)) If this post helps click Yes --------------- Jacob Skaria "stew" wrote: 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 |
Indirect address
(in one line...)
=INDIRECT("'Road Cash Accounting'!"&ADDRESS(10+((ROW()-441)/49-1)*42,COLUMN()+1)) If this post helps click Yes --------------- Jacob Skaria "stew" wrote: 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" wrote: Try (all in one line) =INDIRECT("'Road Cash Accounting'!"&ADDRESS(10+(MOD(ROW(),490)/49)*42,COLUMN()+1)) If this post helps click Yes --------------- Jacob Skaria "stew" wrote: 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 |
All times are GMT +1. The time now is 12:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com