linking a series of cells from one sheet to another.

**jneuspiel** · 11-09-2012, 08:49 PM

I am trying to have a group of cells from one sheet appear in another. Some contain numbers, some contain text and some contain formulas referring to other cells. I can link them one at a time but can't seem to do them en masse.

**FDibbins** · 11-09-2012, 09:01 PM

not sure what you are trying to do, but maybe try this...
use the mouse to reference the 1st instance in the column, then simply copy/drag that reference down (and across?) as far as you need it.

If this is not what you wanted, you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**djapigo** · 11-09-2012, 09:03 PM

Hi jneuspiel,

Once you link one, then just copy that cell down and across to the rest of the cells...

Look out for absolute references... in other words, $ (dollar signs) in the cell reference... for example $A$1

This will fix the link only to A1... so remove the dollar signs then copy the link...

Hope this helps...
Dennis

**jneuspiel** · 11-09-2012, 09:46 PM

Thanks Dennis. The file is attached. All I am trying to do is have the entire "Vertical Program Data and VPH", and "Private/Semi Private Program Data and VPH' Sections from each of my weekly sheets, appear in chronological order on my first 'Summary" sheet. Each contains about 70 or 80 cells and I just want them to appear as they do on their weekly sheets but all in order on the summary sheet. Does that make sense? I do not just want them be copied accross. I want them to be live with the data that is entered into them at any time.

Thanks,

Jan

**FDibbins** · 11-10-2012, 01:21 AM

try replacing you're formula...='Jan 3-8'!J64

with this...
=INDIRECT("'"&A$6&"'!J64")

you can copy this down and across

**jneuspiel** · 11-10-2012, 01:40 AM

Sorry Dennis I was not clear on what I wanted to do.

I want cells from Columns A to I and rows 24 to 33 from each weekly sheet to appear in the same form on the summary sheet. Similarly I want all the cells from Columns A to J and rows 53 to 62 to appear in the same form on the summary sheet. Does that make sense? I am just trying to reproduce those "charts" from each weekly sheet side by side on the summary sheet so that they can be compared by a viewer.

Thanks

**djapigo** · 11-10-2012, 07:53 AM

Hi jneuspiel,

This is not going to be an answer that you want to hear, but there's a little work to set this up, but once you have one linked, then the rest will be easy.

Here's your spreadsheet where I linked 2 of the weekly tabs onto the Summary tab. I'm hoping this is what you want to see. To do the rest, you simply do the 2 following steps.
1. copy cells Summary!A15:J38 to cell W15
2. change cell W15 to =C6 (simply the 3rd tab name you have in cell C6

Repeat these steps to cells AH15, then to AS15, etc.

Let me know if you want me to fully explain how I did this. It was not a magical formula, but I set up one cell, changed the absolute references ($), copy to a full row, slightly modified each formula in the row, then copied the row to the rest of the cells... I'm sure there's a more elegant formula than the one I came up with, but I only came up with this... anyway, let me know if you want more info...

I modified FDibbins' INDIRECT formula which I think works really well in your problem...

Let me know how this works out...

- Dennis

**jneuspiel** · 11-10-2012, 08:08 PM

Thank you Dennis! That worked really well.

The only thing (as you can see in the attached) is that Mar 30-Apr 6 on the Summary sheet for some reason does not seem to work. I assume it is a problem in the sheet of this name but I have not been able to find the cause. Any ideas?

Thanks a bunch!