link a range of cells
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 |
link a range of cells
Hi Ken
Select the range Ctrl-C to copy Right click on the destination cell Paste Special Click on Paste Link -- Regards Ron de Bruin http://www.rondebruin.nl "ken's dilemma" <ken's wrote in message ... 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 |
link a range of cells
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 ken's dilemma wrote: 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 |
link a range of cells
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 "Ron de Bruin" wrote: Hi Ken Select the range Ctrl-C to copy Right click on the destination cell Paste Special Click on Paste Link -- Regards Ron de Bruin http://www.rondebruin.nl "ken's dilemma" <ken's wrote in message ... 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 |
link a range of cells
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 On Thu, 13 Sep 2007 11:40:06 -0700, Meenie wrote: 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 "Ron de Bruin" wrote: Hi Ken Select the range Ctrl-C to copy Right click on the destination cell Paste Special Click on Paste Link -- Regards Ron de Bruin http://www.rondebruin.nl "ken's dilemma" <ken's wrote in message ... 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 |
All times are GMT +1. The time now is 04:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com