alter code to apply to range (links cells w/ row insertions)


P

purplec0ws

Is there any way to alter the following code to apply to a range? Currently,
I am applying this formula to each column, but it would be much easier to do
this for multiple columns at a time.

The code allows one to link a column of cells from sheet to sheet while
recognizing row insertions and deletions.

I took the following from a previous post by 'Max':

Assume the source col to be linked is col A in Sheet1, A1 down

In Sheet2
-------------
Put in any starting cell, say A1:
=OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,)

Copy A1 down as many rows
as the linking is required to col A in Sheet1

This should now "recognize" any subsequent row* insertions / deletions in
Sheet1 (*or even column deletions in Sheet1)
viz. col A in Sheet2 will always point to what's in col A in Sheet1

Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead,
change:

INDIRECT("Sheet1!A1")
to
INDIRECT("Sheet3!B1")

in the formula
 
Ad

Advertisements

B

BSc Chem Eng Rick

HI

Try this, you can copy down and across as far as you need to go.
=OFFSET(INDIRECT("Sheet1!A1"),ROW()-1,COLUMN()-1)

If this helps, please 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

Top