need to replicate insertting a row from 1 worksheet into another

[Guest]

Worksheet "A" is a master inventory list for company's eyes only. Worksheet
"B" is a distribution list for public view and it is all driven by formulas
from "A"
so that I do not have to reinput the same data twice. "B" will have certain
columns and sheets hidden and is password protected. From time to time I
must enhance "A" by inserting a row within the list and adding a new client's
data. I want this row insertion and new data to also copy over to "B"
automatically. Bottom line I need any changes made to "A" to automatically
copy over into "B". Original data is easy to do, but I cannot figure out how
to make adding/deleting a column or row and the new data that is input
automatically copy over into "B".

 

[Guest]

Insert links between A and B.

Start in spreadsheet B, select a cell that has a partner in A (example: A1)

In that cell type =

Open up spreadsheet A, select the partner cell in A (for this example: A1)

Press enter

Voila! You've just created a link between spreadsheet A and spreadsheet B.

To test this, go to spreadsheet A and type something in A1, like 123456.
Then open up spreadsheet B and look at A1, it should say 123456.

The formula you created in A1 can be copied down columns and across rows
until B looks exactly like A. Then just delete the links in B that you don't
want the customers to see.

Hope this is helpful!

Pamela

 

[Guest]

Thanks for the very clear response. Unfortunately, that is the part that I
already do understand. What I am having a problem with is ....here's the
senario....
"A" has 10 rows and 10 columns of data.
After linking formulas, "B" is an exact duplicate of "A".
I acquire a new client and need to list her data in row 5 of "A", which will
mean that "A" now has 11 rows of data....but "B" still only has 10 rows of
data.

I am trying to figure out a way to make "B" automatically insert a new row 5
and link the newly input data from "A". Sorry about not being clearer in my
original question, BUT thanks a bunch for your response. This is the first
time I have ever posted a question.

 

[Guest]

Oh, in that case, just copy the links down to say, row 50, or however far
down you think you'll need.

 

[Guest]

You are the greatest thing since sliced bread! But if I do that I wind up
with a lot of cells, i.e., rows 12 - 50 with "0's" in them. Is there any way
to hide them so the spreadsheet looks cleaner? Thanks!

 

[Guest]

Hide all zero values on a worksheet

On the Tools menu, click Options, and then click the View tab.

To display zero values as blank cells, clear the check box.

*mUah!*

 

[Guest]

Hey, got rid of the zeros, thanks! But my original problem is still there.
I tried lyour suggestion of copying the links down through row 50 on "B".
Then I went back to "A" and inserted a row in the middle of the list, which
became my new row 6 in my now new set of 11 rows. I accessed "B" hoping to
see that the new client data from the new row 6 now appeared in "B"....it did
not. Any more suggestions, thanks.

 

[Guest]

OK..how about simplying having spreadsheet "B" already formated with the data
I do not want to initially share with prospective buyers hidden, then
whenever I make a change on spreadsheet "A", I save it as "A" and then save
it again as "B". Then I can immediately distribute "B" It's all in the
"saving" and that way I don't have to find a way for the program to do it for
me. Unless I am really overlooking the obvious. Ha! What do you think?

 

[Guest]

Yes. When you open up spreadsheet B and get a message asking you if you want
to update the spreadsheet, click yes.

And in response to your earlier post, you're right... you could make life a
lot easier by just making and saving changes to spreadsheet A and then doing
a "save as" and save it as spreadsheet B. Then delete the info you don't
want your customers to see and be done with it.

 

[Guest]

I don't get a message asking if I want to update the spreadsheet B. What am
I doing wrong?

 

[Guest]

Sorry, I was wrong. When you link between workbooks, it creates absolute
references to the information in workbook A. For example, you'll see the
following in cell A1 in spreadsheet B.

=[test1.xls]Sheet1!$A$1

Remove the absolute references ($) so that it reads as follows:

=[test1.xls]Sheet1!A1

then copy that formula for all the columns/rows that you think you'll need
and when you insert a row, the values will update automatically.

 

[Guest]

Sorry...I removed the absolute reference to all cells in worksheet B. I
additionally linked formulas for 5 additional rows of data. I confirmed they
do not have the absolute reference. Then went back to worksheet A and
inserted a row half way down and input data. Saved it and went to Worksheet
B....no change. The new row of data I input on A did not copy over to B.
What next? Thanks for hanging in!

Sorry, I was wrong. When you link between workbooks, it creates absolute
references to the information in workbook A. For example, you'll see the
following in cell A1 in spreadsheet B.

=[test1.xls]Sheet1!$A$1

Remove the absolute references ($) so that it reads as follows:

=[test1.xls]Sheet1!A1

then copy that formula for all the columns/rows that you think you'll need
and when you insert a row, the values will update automatically.

I don't get a message asking if I want to update the spreadsheet B. What am
I doing wrong?

 

[Guest]

GRRRRR! Ok, I did some tests and I'm sorry to say, I don't have an answer
for you. It seems like Excel doesn't like for you to insert a row between a
linked spreadsheet because it assumes that you want all the data to remain
linked amongst the original rows references. What a burn!!!

Here's my last trick, and it works because I tested it before I wrote this
reply.

Make whatever changes to A. Then go to cell A1 in spreadsheet B that does
NOT contain absolute references (so it should look something like this
=[test1.xls]Sheet1!A1). Click and drag the forumla down the rows and across
the columns and the forumlas will reset to match A. You'll have to do this
"formula wipe" each time you insert a row in A since adding an additional row
to A will simply change the cell reference in B. Try that and let me know
how it goes.

OR

Do a save as, delete what the customers shouldn't see and be done.

Pamela

Sorry...I removed the absolute reference to all cells in worksheet B. I
additionally linked formulas for 5 additional rows of data. I confirmed they
do not have the absolute reference. Then went back to worksheet A and
inserted a row half way down and input data. Saved it and went to Worksheet
B....no change. The new row of data I input on A did not copy over to B.
What next? Thanks for hanging in!

Sorry, I was wrong. When you link between workbooks, it creates absolute
references to the information in workbook A. For example, you'll see the
following in cell A1 in spreadsheet B.

=[test1.xls]Sheet1!$A$1

Remove the absolute references ($) so that it reads as follows:

=[test1.xls]Sheet1!A1

then copy that formula for all the columns/rows that you think you'll need
and when you insert a row, the values will update automatically.

I don't get a message asking if I want to update the spreadsheet B. What am
I doing wrong?

:

Yes. When you open up spreadsheet B and get a message asking you if you want
to update the spreadsheet, click yes.

And in response to your earlier post, you're right... you could make life a
lot easier by just making and saving changes to spreadsheet A and then doing
a "save as" and save it as spreadsheet B. Then delete the info you don't
want your customers to see and be done with it.

:

Hey, got rid of the zeros, thanks! But my original problem is still there.
I tried lyour suggestion of copying the links down through row 50 on "B".
Then I went back to "A" and inserted a row in the middle of the list, which
became my new row 6 in my now new set of 11 rows. I accessed "B" hoping to
see that the new client data from the new row 6 now appeared in "B"....it did
not. Any more suggestions, thanks.

:

Oh, in that case, just copy the links down to say, row 50, or however far
down you think you'll need.

:

Thanks for the very clear response. Unfortunately, that is the part that I
already do understand. What I am having a problem with is ....here's the
senario....
"A" has 10 rows and 10 columns of data.
After linking formulas, "B" is an exact duplicate of "A".
I acquire a new client and need to list her data in row 5 of "A", which will
mean that "A" now has 11 rows of data....but "B" still only has 10 rows of
data.

I am trying to figure out a way to make "B" automatically insert a new row 5
and link the newly input data from "A". Sorry about not being clearer in my
original question, BUT thanks a bunch for your response. This is the first
time I have ever posted a question.

:

Insert links between A and B.

Start in spreadsheet B, select a cell that has a partner in A (example: A1)

In that cell type =

Open up spreadsheet A, select the partner cell in A (for this example: A1)

Press enter

Voila! You've just created a link between spreadsheet A and spreadsheet B.

To test this, go to spreadsheet A and type something in A1, like 123456.
Then open up spreadsheet B and look at A1, it should say 123456.

The formula you created in A1 can be copied down columns and across rows
until B looks exactly like A. Then just delete the links in B that you don't
want the customers to see.

Hope this is helpful!

Pamela

:

Worksheet "A" is a master inventory list for company's eyes only. Worksheet
"B" is a distribution list for public view and it is all driven by formulas
from "A"
so that I do not have to reinput the same data twice. "B" will have certain
columns and sheets hidden and is password protected. From time to time I
must enhance "A" by inserting a row within the list and adding a new client's
data. I want this row insertion and new data to also copy over to "B"
automatically. Bottom line I need any changes made to "A" to automatically
copy over into "B". Original data is easy to do, but I cannot figure out how
to make adding/deleting a column or row and the new data that is input
automatically copy over into "B".