Excel Linking Question

[cubs610]

Hello All,


Question about links. I am trying to create a link between spreadsheets that will add new cells inserted into a range. Normally I just copy the range, paste special, paste links no problem. Here is the twist. Let say you have two sheets (SH1 and SH2). If I copy a range of cells (say for example A5:10), then link them to SH2. How can I get the range on SH2 to adapt if I add in a row on SH1. (SH1 Range = A5: A11, cell was inserted in the middle of the range). I tried using named ranges, but since they are absolute, no luck.... any thoughts?


Thanks,

D

 

[Luke M]

I tried using named ranges, but since they are absolute,

Who said they have to be? <grin>


Define a named range "MyList" as so:

=OFFSET(SH1!$A$5,0,0,COUNTA(SH1!$A$5:$A$1000))

Note that this range will grow in size as more data is entered in cells A5:A1000


Then, in SH2, 1st formula is:

=IF(ROWS(A$1:A1)>ROWS(MyList),"",INDEX(MyList,ROW(A1)))

Copy down as far as you would ever need.

 

[cubs610]

@LukeM


No doubt you have earned the title of Grand Master Excel Ninja.


Many thanks,

D

 

[Hui]

D


What you are using is known as Dynamic Ranges


they are discussed here: http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/

 

[Luke M]

Thanks, D. Although I might have to still defer that title to Hui. =)