Replicating Cells That Link Elsewhere

[John Gregory]

I'm making an involved spread that starts with a master stock portfolio then
transfers portions to other worksheets. The master gets updated monthly but
it's structure is expected to change often as stocks enter and exit the mix.
This forces reformatting the secondary sheets to which some of the data
flows. For example. In the first month, the very first stock in the
portfolio began with a "C". This month, although that stock is still in the
mix, the first stock in the alphabetically listed portfolio begins with an
"A". Thus, all the secondary sheets need altered.

Inserting and deleting lines is simple, however, linking each cell is
tedious because everything is now linked to a newly downloaded master list
that had to be created ... which caused changes in the structure to the
secondary sheets as well since some stocks have been added or dropped from
the secondary sheets. (The secondary sheets represent the portfolios of two
separate groups of club members competing among each other. The Master
portfolio is the sum of the two.... with a small cash reserve unassigned.)

Once I get the master the realigned and linked to the newly downloaded sheet
that feeds the stock prices and stats, inserting and deleting lines in the
secondary sheets takes place. Then I have to relink each cell to its
respective cell on the new master. The task could be simplified it I knew of
a command that will allow me to shade several cells, hit the = sign, and
link that block of cells back tot he master as a block. If there is a way to
do that, I haven't found it; I'm forced to link each cell one at a time. Any
suggestions?

 

[Ken Wright]

Select range, Edit / Copy, select destination cell for upper left cell, then
Edit / Paste Special / Paste Link Button??

 

[John Gregory]

That doesn't work, Ken. I'm trying to bring in a block of cells by hitting
the equal sign (=) in a given cell then linking that to comparable block of
cells from another worksheet. IT only transfers one block... and that's in
error because it tries to put the entire range in that one cell.

 

[Ken Wright]

Is your block of cells a contiguous range, or are the cells spread across the
sheet? If the former, then you cannot do it the way you are trying, but the way
I suggested will allow you to do so. Or, you can select say an 8x5 block of
cells, type =, select a target block of cells 8x5, hit CTRL+SHIFT+ENTER and it
will link the block, though there is little difference between the end result
except that one is an array.