Create new worksheet which looks identical to another worksheet and links to it

[ThoughtIwasSmart]

Using Excel X (current version: 10.1.5)

In a workbook (call it "B"), I want to have it contain a worksheet
("B") that is an exact duplicate of a worksheet ("A") in workbook "A".
The worksheet is rather small - it only has 11 columns and 50 rows.

Here are the steps I take, and the results.
I open both worksheets. Worksheet A has data and formatting (some red
colored cells, some cells with red colored text). Worksheet B is
blank (new worksheet).

Cell A1 is red colored, and has the word "Mighty Words" in
larger-than-usual font
Cell B1 is red colored, but has no data
"Mighty Words" is too large to fit in the cell width so it flows into
cell B1
(all this is normal)

What I want to do now is have worksheet B look identical to A (each
and every cell looks like that seen in A), using the fewest steps.
And, if changes are made in A, I want the changes to occur in B.

I go to worksheet B. I select cell A1 (currently blank). I enter an
"=" in the formula bar and select the same cell in A. I get the data
("Mighty Words") in A1. However, it doesn't have A's formatting (the
cell is not red colored and the text size is normal). This is not
what I want - it should look like worksheet A.
Note: if I do a copy of cell A1 (in worksheet A) and paste it into
cell A1 of worksheet B, it correctly pastes the data and the format,
BUT it doesn't have any reference (in the formula bar) to worksheet A.
So, if I change the words in worksheet A, worksheet B doesn't change.

Questions:
How do I create a worksheet ("B") that will have all of the correct
data and formatting of each of the cells in worksheet "A", and,
maintain a link to each of the cells so that changes in worksheet A
dynamically change the cells in worksheet B. How do I do this in the
fewest number of steps?
(I can't seem to locate the answer anywhere, but it seems like this is
something quite a users would want to do!).

 

[Bernard REY]

You're not that far! Click on the "cell" just where the rows and colum
header join (right above the "1" and left to "A") thus selecting you whole
worksheet. Copy it. Click in cell A1 of the second sheet and go to the
"Paste Special..." item in the Edit Menu. Check the "Format" button. Then
click the "OK" button.

Now, back to the first sheet, select your used range (A1:K50 or so). Copy
it. Click in cell A1 of the second sheet and go to the "Paste Special..."
item in the Edit Menu. Click the "Paste Link" button.

Here you are...


---------------------------------------------------------
Please reply to the newsgroup, and within the same thread.
Merci de répondre au groupe, et dans l'enfilade.

--
Bernard Rey - Toulouse / France
MVP - Macintosh




ThoughtIwasSmart wrote :

 

[ThoughtIwasSmart]

Monsieur Rey,

Thank you for the quick response and words of encouragement. However,
I followed the steps you provided and I still don't get the desired
effect. Cells which are blank in worksheet A, have "0"'s (zeroes) in
worksheet B after the link is performed. How do I do the link without
blank cells being filled in with zeroes?
By the way, I tried reversing the order, doing the 'link' first then
the format, but I ended up with the same result.
Other attempts: On individual cells I tried using the following
formula in worksheet B, but it still did not leave the cell 'blank'
(it inserted a space) - and, doing it for each and every cell would be
very time intensive:
= IF(ISBLANK('[workbook A]worksheet A'!A1),"",'[workbook A]worksheet
A'!A1)

So, I am still not ending up with a worksheet that looks the same as
the original. What am I overlooking? Suggestions?

 

[Bernard REY]

ThoughtIwasSmart wrote :

Thank you for the quick response and words of encouragement. However,
I followed the steps you provided and I still don't get the desired
effect. Cells which are blank in worksheet A, have "0"'s (zeroes) in
worksheet B after the link is performed. How do I do the link without
blank cells being filled in with zeroes?

There's several possibilities, depending upon circumstances and expected
results. One of them being the attempt you describe, but it requires a
slight change:

Other attempts: On individual cells I tried using the following
formula in worksheet B, but it still did not leave the cell 'blank'
(it inserted a space) - and, doing it for each and every cell would be
very time intensive:
= IF(ISBLANK('[workbook A]worksheet A'!A1),"",'[workbook A]worksheet
A'!A1)

Take away the space between the two quotes (if there is one, I can't see one
here) and your cell will be displayed empty. It's quite fast to extend your
changes copying and pasting the formula.

Another possibility is in the "Preferences" > "View" pane, where you can
uncheck the "Zero values" checkbox. The counterpart is that it won't display
anything in cells where you might want to see a zero...

A third possibility would be to use the "Format" > "Conditional Formatting"
options.

So, I am still not ending up with a worksheet that looks the same as
the original. What am I overlooking? Suggestions?

Much lies in the meaning you give to "looks the same", in fact...

 

[ThoughtIwasSmart]

Monsieur Rey,

I tried your suggestions to no avail. I suspect you know this
application very well, and, you are the only one who has responded to
my plight, so I appreciate your support. I consider myself a very
proficient person, but the simplicity of this dilemma is confounding
me!

Your previous response ended with:
"Much lies in the meaning you give to "looks the same", in fact... "

I would appreciate it if you would endulge me by trying the following
experiment and let me know what results you get. It should only take
a moment, so I would appreciate your trying it out. It will take me
longer to explain than for you to perform the actions. So, here we go
step-by-step:

1) Create a new workbook with at least 2 worksheets (mine defaults to
3 worksheets - each one has default column widths of "10.00")
2) On 'Sheet1', type the following things (don't include the quotes):
A1: "My dog has many spots and likes to sleep on the sofa"
A2: "8" (the number 8)
B2: "10" (the number 10)
Don't put anything in B1
3) On 'Sheet2' make each of the 4 cells equal those in 'Sheet1', by
using "=" in the formula bar:
cell A1's formula shows "=Sheet1!A1"
cell A2's formula shows "=Sheet1!A2"
cell B1's formula shows "=Sheet1!B1"
cell B2's formula shows "=Sheet1!B2"
4) Go back to Sheet1 and delete the "10" in cell B2 .. the cell should
appear blank (like cell B3)
5) Compare Sheet1 and Sheet2
Here are my results:

Sheet1:
In A1 (through E1) it shows "My dog has many spots and likes to sleep
on the sofa"
A2 shows "8"
B2 is blank

Sheet2:
In A1 it shows "My dog has m" (the sentence is cut short because of
data in B1)
B1 has a "0" (zero)
A2 shows "8"
B2 shows "0" (zero)
........ Clearly, these two sheets do not look identical .. cells A1,
B1, and B2 are totally different.

What results do you get? If they are the same as above, then your
worksheet is behaving like mine. Please let me know what steps you
take to correct it, while maintaining the links (Sheet2 always looks
like Sheet1 when changes are made to Sheet1)? (I tried using all of
your previous suggestions to no avail)

On the other hand, if both Sheets look identical (step 5 above), then
I would like to discuss your default settings because we should be
getting similar results.

Thanks in advance for trying this out .. I am very anxious to reach a
resolution.

 

[Bernard REY]

I see! The A1 case is THE case when you'll meet a problem, as the B1 cell is
not blank, not enough to let the A1 text fold over it.

In this case, you should either leave the cells (in the second sheet) blank,
or merge them in order to have the expected display.

Is the display in cells B1:E1 conditional or so? You may have to adapt your
formulas somewhat then.

 

[JE McGimpsey]

Here's one way, perhaps.

Set up your sheets as in your example.

On sheet2, Set your font color to the background color.

On Sheet1, Select your range (e.g., A1:E2) and choose Edit/Copy.

Select Sheet2!A1. Hold the shift key down and choose Edit/Paste Picture
Link.

the cells in Sheet2 will now have the values from Sheet1, but only the
picture link (which by definition should be identical to Sheet1 unless
you resize it) will display in Sheet2.

 

[ThoughtIwasSmart]

JE,

I appreciate your suggestion, using a Picture Link. If I change a
value on Sheet1 it 'appears' to cause the values on Sheet2 to change
as well (good).
However, appearances are deceiving (not good).
Since Sheet2 has a Picture Link (A1 thru B2), the cells A1 thru B2 on
Sheet2 do not actually have the same values as the cells on Sheet1.
So cell values that underlie the Picture Link can't be used elsewhere.
For example, if I select a cell outside of the Picture Link, e.g. E6
(Sheet2), and set its formula to "=A2", it doesn't provide the same
value which I 'see' in A2 ("8"). Instead, it gives me a "0" (zero).
So, I am no longer able to use the values, which I see, for
computations elsewhere. All of the cells (A1 thru B2) return "0".
Hence, using a Picture Link still does not resolve the issue. Thanks,
anyway, for playing.

 

[ThoughtIwasSmart]

JE,

Please disregard my last response.
I overlooked a small(?) detail of your instructions (having the data
already linked to Sheet2 - with same background color) - before
performing the Picture Link.

It works !!
This is one heck of a workaround for what would initially seem like a
trivial issue!

If you, or anyone else, knows of another method which doesn't use
Picture Link as a resolution, I am open to alternatives.

Performing this task is something Microsoft might want to consider
including as built-in feature (creating a new worksheet with all
formats and data automatically linked).

Thank you very much for responding to my inquiry and coming up with a
resolution!! Keep up the good work !!!