Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
alter code to apply to range (links cells w/ row insertions)
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 -- Rgds Max xl 97 --- |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
alter code to apply to range (links cells w/ row insertions)
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" <<<<<<<<<<< "purplec0ws" wrote: 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 -- Rgds Max xl 97 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i apply Goal Seek to a range of cells in Excel | Excel Worksheet Functions | |||
Alter pivot with code | Excel Discussion (Misc queries) | |||
modify a macro to apply to a specific range of cells | Excel Discussion (Misc queries) | |||
How do i a apply range(autoformat) in non-adjacent cells? | New Users to Excel | |||
How to apply rounding across a range of cells with other formulae | Excel Worksheet Functions |