How to Link Sheets so One Reflects changes to the Other

[gabribasa]

Hi Mr. Excel,

I'm using Excel 2007 and Windows XP.

1. General Question: How to create a "link" between sheets so that one sheet refelcts changes I make to the first one. (I have tried copy and pasting and creating sheet references like "SheetName!B6")

2. Background Info:
a. Currently I have a system in place to update customer "Checklists." Once we "Have" an item, we put an "X" in the have box, and then hide the row for each thing we have.
We then update the customer by converting the excel document to a pdf and we send it to them.

b. I would like to have one sheet be the master sheet for our reference, without any cells hiden, and have another sheet reflect any changes we make to the checklist but be the one we hide rows in and subsequently make the PDF copy of.

3. Specific Values:
The sheet I would like make a dynamic "copy" of has columns A-E and rows 1-129. (Though sometimes that varies.)

 

[Plastik808]

What code have you done for it so far?

 

[gabribasa]

I have so far (to keep the formatting,) made a copy of the Sheet I would like to have a dynamic copy of.

->Right click on the sheet, -> Select "Move or Copy" -> Make a Copy of it in the same Excel document.

Then I tried:

Selecting B6 in the New Sheet Copy, typing =SheetName!B6 as the formula.

I don't believe this creates a dynamic link though, because when I went to the first sheet and edited cell "B6", the changes were not reflected in B6 in the New Sheet. It is also very tedious to go through every cell and type in that formula.

Do you know how I could do this?

Thanks so much!

 

[LCTrucido]

For this example your master sheet is called "Master" and the copy is called "Copy". Inventive, I know.

Assuming you want cell A1 in Copy to reflect cell A1 in Master:
1. Open both sheets.
2. Select cell A1 in Copy.
3. Type "=" (without quotes, of course).
4. Select cell A1 in Master.
5. Hit Enter.

This will create a reference in Copy to obtain the information in Master every time you open Copy. This will only work properly if you don't change the file directory of Master once the references have been created. If you do move it, you'll have to update all of the references in the Copy.

 

[gabribasa]

Thank you! This does work. Is there any way to do this on a larger scale though? Or do I have to insert that for every cell? It seems kind of tedious...

 

[gabribasa]

Also, if I link a cell that has nothing, a "zero" shows up in the Copy Sheet. Is there any way to just have nothing show up, the way it does in the "Master" sheet?

 

[gabribasa]

Another "Also":

If I want to edit the "Master Sheet" and I want to delete a row, how could I have that work dynamically with the "Copy Sheet?"

 

[Saagar]

You don't have to repeat that for every cell. Extending the example given by LCTrucido, once you create the formula in cell A1 in "Copy" sheet, follow these steps.

6. In the "Copy" sheet, right click on cell A1 and click on "Copy" option
7. Select the range A1 to E129
8. Right click in the range and click on "Paste" option.

Now cells A1 - E129 in Copy sheet will reference the corresponding cells in Master sheet.

 

[LCTrucido]

You don't have to repeat that for every cell. Extending the example given by LCTrucido, once you create the formula in cell A1 in "Copy" sheet, follow these steps.

6. In the "Copy" sheet, right click on cell A1 and click on "Copy" option
7. Select the range A1 to E129
8. Right click in the range and click on "Paste" option.

Now cells A1 - E129 in Copy sheet will reference the corresponding cells in Master sheet.

Another way to do it is create the entire Master sheet. Once it's done:

1. Open the blank Copy sheet.
2. Right click on the Master worksheet tab and select "Move or Copy".
3. Open the drop down menu and select the Copy worksheet.
4. Check the box at the bottom that says "Make a Copy".
5. Click OK.

This will copy the entire Master worksheet to the Copy file, but will automatically create the cell references in the Copy sheet.

 

[LCTrucido]

Also, if I link a cell that has nothing, a "zero" shows up in the Copy Sheet. Is there any way to just have nothing show up, the way it does in the "Master" sheet?

You could alter the formula to an IF statement to return "" if the cell contents are 0, then use Saagar's method to fill the other cells. You could also use conditional formatting. The clause would be Cell = 0, then change the font to white (or whatever your background color is.)

Another "Also":

If I want to edit the "Master Sheet" and I want to delete a row, how could I have that work dynamically with the "Copy Sheet?"

The Copy worksheet will update every time you open it, showing you changes to Master if you've saved them. If you delete a row in Master and then update Copy, you'll see a row of zeros at the bottom. This is because Copy still sees the cell reference, you'll have to remove the zeros manually or use one of the methods mentioned above.

Sorry for the multiple posts but concerning this from the previous:

This will copy the entire Master worksheet to the Copy file, but will automatically create the cell references in the Copy sheet.

Should say it will automatically create the references for any cells containing formulas. Manual values will be copied over as is.