link a range of cells

[Guest]

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

 

[Ron de Bruin]

Hi Ken

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

 

[ryan.c.christiansen]

If the ranges are the same size, you can use an array function.

For example, if the original range on Sheet 1 is A1:A5, then on Sheet 2
select a range of five cells and enter the following formula.

=Sheet1!A1:A5

and press CTRL-SHIFT-ENTER to enter the formula into the cells as an
array function, thereby creating an array range.

A nice side effect to this is that you can't change individual cells in
the array range, which helps to prevent mistakes.

Is that what you're looking for?

-Ryan

 

[Guest]

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

 

[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