Home |
Search |
Today's Posts |
#1
|
|||
|
|||
need to replicate insertting a row from 1 worksheet into another
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". |
#2
|
|||
|
|||
need to replicate insertting a row from 1 worksheet into another
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 :) "giterdun" wrote: 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". |
#3
|
|||
|
|||
need to replicate insertting a row from 1 worksheet into anoth
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. "pameluh" wrote: 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 :) "giterdun" wrote: 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". |
#4
|
|||
|
|||
need to replicate insertting a row from 1 worksheet into anoth
Oh, in that case, just copy the links down to say, row 50, or however far
down you think you'll need. "giterdun" wrote: 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. "pameluh" wrote: 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 :) "giterdun" wrote: 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". |
#5
|
|||
|
|||
need to replicate insertting a row from 1 worksheet into anoth
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! "pameluh" wrote: Oh, in that case, just copy the links down to say, row 50, or however far down you think you'll need. "giterdun" wrote: 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. "pameluh" wrote: 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 :) "giterdun" wrote: 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". |
#6
|
|||
|
|||
need to replicate insertting a row from 1 worksheet into anoth
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!* "giterdun" wrote: 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! "pameluh" wrote: Oh, in that case, just copy the links down to say, row 50, or however far down you think you'll need. "giterdun" wrote: 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. "pameluh" wrote: 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 :) "giterdun" wrote: 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". |
#7
|
|||
|
|||
need to replicate insertting a row from 1 worksheet into anoth
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. "pameluh" wrote: Oh, in that case, just copy the links down to say, row 50, or however far down you think you'll need. "giterdun" wrote: 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. "pameluh" wrote: 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 :) "giterdun" wrote: 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". |
#8
|
|||
|
|||
need to replicate insertting a row from 1 worksheet into anoth
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? "pameluh" wrote: 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!* "giterdun" wrote: 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! "pameluh" wrote: Oh, in that case, just copy the links down to say, row 50, or however far down you think you'll need. "giterdun" wrote: 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. "pameluh" wrote: 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 :) "giterdun" wrote: 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". |
#9
|
|||
|
|||
need to replicate insertting a row from 1 worksheet into anoth
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. "giterdun" wrote: 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. "pameluh" wrote: Oh, in that case, just copy the links down to say, row 50, or however far down you think you'll need. "giterdun" wrote: 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. "pameluh" wrote: 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 :) "giterdun" wrote: 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". |
#10
|
|||
|
|||
need to replicate insertting a row from 1 worksheet into anoth
I don't get a message asking if I want to update the spreadsheet B. What am
I doing wrong? "pameluh" wrote: 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. "giterdun" wrote: 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. "pameluh" wrote: Oh, in that case, just copy the links down to say, row 50, or however far down you think you'll need. "giterdun" wrote: 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. "pameluh" wrote: 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 :) "giterdun" wrote: 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". |
#11
|
|||
|
|||
need to replicate insertting a row from 1 worksheet into anoth
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. "giterdun" wrote: I don't get a message asking if I want to update the spreadsheet B. What am I doing wrong? "pameluh" wrote: 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. "giterdun" wrote: 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. "pameluh" wrote: Oh, in that case, just copy the links down to say, row 50, or however far down you think you'll need. "giterdun" wrote: 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. "pameluh" wrote: 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 :) "giterdun" wrote: 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". |
#12
|
|||
|
|||
need to replicate insertting a row from 1 worksheet into anoth
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! "pameluh" wrote: 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. "giterdun" wrote: I don't get a message asking if I want to update the spreadsheet B. What am I doing wrong? "pameluh" wrote: 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. "giterdun" wrote: 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. "pameluh" wrote: Oh, in that case, just copy the links down to say, row 50, or however far down you think you'll need. "giterdun" wrote: 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. "pameluh" wrote: 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 :) "giterdun" wrote: 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". |
#13
|
|||
|
|||
need to replicate insertting a row from 1 worksheet into anoth
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. :P 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. :P Pamela :) "giterdun" wrote: 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! "pameluh" wrote: 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. "giterdun" wrote: I don't get a message asking if I want to update the spreadsheet B. What am I doing wrong? "pameluh" wrote: 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. "giterdun" wrote: 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. "pameluh" wrote: Oh, in that case, just copy the links down to say, row 50, or however far down you think you'll need. "giterdun" wrote: 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. "pameluh" wrote: 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 :) "giterdun" wrote: 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". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect Workbook vs Worksheet?? | Excel Worksheet Functions | |||
How do I replicate information from one worksheet to another? | Excel Discussion (Misc queries) | |||
Inserting Filtered RC cell information into other worksheets | Excel Discussion (Misc queries) | |||
Copy from worksheet to another x times | Excel Discussion (Misc queries) | |||
copyright and worksheet protection | Excel Discussion (Misc queries) |