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.)

 

[Snakehips]

gabribasa,

Saagar's solution is perfectly valid.

There is often more than one way to skin a cat so here are two other options.

You could enter the =Master!A1 formula in cell A1 of your Copy sheet, as per LCTrucido, then select cell A1 in the Copy sheet. When over the bottom right corner of the selected cell the cursor will become + and you can drag incremented formulas across as many columns as you wish. Then if select the new range of cells in row 1 that contain these formulas you can drag down as many rows as you wish.

Alternatively, and possibly the best method is to:
1 Select the full range that you wish to link to in your Master sheet and 'right click / copy'.
2 Select the appropriate top left cell of the destination range within your Copy sheet.
3 With this cell, Right Click / Paste Special / Click the Paste Link button in the left bottom corner and you are done.

Note in the Paste Special dialog box all the options you have to selectively paste other attributes of the source range.

With regards to the linked empty cells showing as 0.
The 'en mass' way to inhibit the zeros is:
Office Button/Excel Options/Advanced/Scroll down to 'Display options for this worksheet'
Ensure the correct worksheet is selected in the dropdown then UN-tick the box below that says 'Show a zero in cells that have zero value' and click OK.

If you need to be more selective than that with zero values you will need to have a different approach and more complex formulas.

Hope this helps?

 

[Snakehips]

With regards to deleting rows from the link source ie your Master sheet. That will destroy some of the established links and result in hashRef! errors.
You will be better off Clearing Contents of cells/rows in the Master to get rid of unwanted data. You can then hide blank rows and or sort blank row to the bottom of your sheet if you wish. If any of these tasks are considered tedious and are performed often then it is easy to establish a Macro to automate things a little.

 

[gabribasa]

Question Below in Blue

Thanks for your responses! They were very helpful, and I have successfully made a "Master" sheet which is replicated in an adjacent sheet called "Copy."

I did this by:

1. Making a complete copy of the "Master" sheet by right clicking and copying.
2. Entering the formula in cell A1, filling over, and then filling down for every Cell needed.
3. Reformatting using the cell styles I created for quick "remormatting."

Question:
My next question is, is there any way to "link" the formatting? Cell Styles are great for quickly changing formatting, but I don't want to have to change the formatting in both sheets if I make any changes.

(For example, if I add a row to the "Master Sheet", can it somehow be carried over to the "Copy Sheet" in text AND format?)

Thanks so much!

 

[Snakehips]

If you hold down the Ctrl key you can click to select as many sheets as you wish. Then any changes that you make to the visible sheet will be made in all of the selected sheets. However, this does not create any linking, just hard copy, and if you insert a row in the Master sheet between existing linked rows you are likely to displace the existing links.

I wonder if a Copy sheet is really the best solution for you if you are going to be making so many changes to the Master. I feel as if a little automated manipulation of the Master might be a better approach.

Are you able to expand a little on your requirements?

Using Master and Copy sheets you will be doubling up on every checklist?
Is the Copy sheet purely to use as the basis for customer pdf?
Does the Copy contain any data/ formatting that is not as per the Master or is it just that you want one version with rows hidden and one without
Is there more than 1 Master, ie 1 per several customers?
How many customers?
How many items per checklist?

 

[Saagar]

Looking at the "Background Info" you posted in the thread originating post, after a little thought, I think the best thing to do is to use VBA macro to toggle between "hiding" and "showing" selected rows. This way you will use only one sheet with the comfort of looking at all the data and hiding rows when generating customer PDFs.

Do you have any VBA experience?