Incremental Indirect formula

**Johnmus** · 09-02-2012, 09:28 PM

Hi All

I have names and data in the Event Info sheet which transfers to sheet2 with =INDIRECT("'Event Info'!B27")
This works - using ='Event Info'!B27 would break the link if using cut and paste on the Event Info sheet. How can I make the formula incrememtal i.e. copy down would change B27 to B28 etc.?

Thanks

**Ron Coderre** · 09-02-2012, 10:04 PM

Perhaps something like this...
This regular formula returns the value from the 27th row of Col_B on the Event Info sheet, even if rows are deleted/added or cells are moved into that area.

Please Login or Register  to view this content.

Note: that formula is NOT durable against deleting column A or B on the Event Info sheet.

If that's an issue then, try this regular formula which is durable against column/row deletions/additions on the Event Info sheet:

Please Login or Register  to view this content.

Is that something you can work with?

**Johnmus** · 09-03-2012, 02:32 PM

Yes, Thank You very much! But could you explain to me why I have to go to minus rows. There are 13 possible entries in Event Info sheet Column B,C,D,E,F and G and these are transferred from Event Info sheet to Column B of sheet 2.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I seems to work, but would appreciate any information on this process.
Thank You!

**BenMiller** · 09-03-2012, 04:18 PM

You can always just use ROW(A23) or ROW(A10) etc.

If you think you might delete column A, just shove it all the way out in left field: ROW(AZ23) etc.

You can also use this in the INDIRECT function:

=INDIRECT("'Event Info'!B"&ROW(AZ27))