link a range of cells

K

ken's dilemma

how do i link a range of cells in one worksheet to a range of cells in
another worksheet? i want the cells to update as i change the originals. i
can link one cell..but when i try to link a range it gives me the #VALUE.
thanx
 
R

Ron de Bruin

Hi Ken

Select the range
Ctrl-C to copy
Right click on the destination cell
Paste Special
Click on Paste Link
 
M

Meenie

Hi Ron,
I'm not the original poster but was having the same problem. This works
great for me except for one glitch.
The info in the cells I'm copying from are percentages with 2 decimal
points. the sheet if for a months worth of data broken down week by week.
So say A1 is the date from the first week of the month, A2 the second week.
Then at the end of the month in A5 is a formula to get the average for the
month.
the problem is when I copy from the first sheet, the cells that don't have
data in them YET come over to the second sheet as 0.00% which messes up the
running average. (On the sheet I'm copying from, before data is entered the
cell is just blank and doesn't affect the running average.) How can I make
the blank cells cpy as blank and change to the correct number when the data
is entered?
I hope this makes some kinda sense, lol.
Thanks, Meenie
 
G

Gord Dibben

You have to trap for blank cells.

You cannot do that using paste link.

In A1 of Sheet2 enter =IF(Sheet1!A1="","",Sheet1!A1)

Drag/copy this down or across or both to link to Sheet1

Your average will exclude those blank cells.


Gord Dibben MS Excel MVP
 
Top