Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking Cells from different worksheets
Thank you in advance to anyone who attempts to answer this post. I have a
total of 6 excel spreadsheets that I would like to link to each other. 5 of them could be considered 'child' sheets and one 'parent' or master sheet. On the 5 child sheets, each will contain text and numbers and a sum at the end. On the parent sheet I would like to have 6 tabs. One tab for each child sheet that is a constantly updating copy of changes made in child sheet. After those 5 tabs I would like to have a 6th tab that is a grand total of the previous 5 tabs. It should contain copies of all the other 5 tabs, one on top of the other, and then a sum total at the end. I would then like to have a copy of that 'total tab' on 2nd tab on each of the child spread sheets. Is something like this possible? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking Cells from different worksheets
If you try something like this, I think you're setting yourself up for failure.
Way too many things can go wrong. I would take a different approach. Put all your data in one worksheet (the parent worksheet). Do all your data entry there. If you need to view different "slices" of your data, maybe you can use data|filter|autofilter to view the stuff you want to see. If you absolutely must have multiple sheets, then I would still keep the data in one worksheet (and update it there), but use a macro to split the parent's data into each of the child worksheets. You may want to look at the way Ron de Bruin and Debra Dalgleish approached it: Ron de Bruin's EasyFilter addin: http://www.rondebruin.nl/easyfilter.htm Code from Debra Dalgleish's site: http://www.contextures.com/excelfiles.html Create New Sheets from Filtered List -- uses an Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.xls 35 kb Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- replaces old data with current. AdvFilterCity.xls 55 kb If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm JasonG wrote: Thank you in advance to anyone who attempts to answer this post. I have a total of 6 excel spreadsheets that I would like to link to each other. 5 of them could be considered 'child' sheets and one 'parent' or master sheet. On the 5 child sheets, each will contain text and numbers and a sum at the end. On the parent sheet I would like to have 6 tabs. One tab for each child sheet that is a constantly updating copy of changes made in child sheet. After those 5 tabs I would like to have a 6th tab that is a grand total of the previous 5 tabs. It should contain copies of all the other 5 tabs, one on top of the other, and then a sum total at the end. I would then like to have a copy of that 'total tab' on 2nd tab on each of the child spread sheets. Is something like this possible? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking Cells from different worksheets
Thanks for the insight Dave. The reason I wanted 5 different 'child'
spreadsheets is because we have 5 different people that would be updating these sheets through out the day. Unless I am mistaken, if one person has the master sheet open then the other people can not make changes? Is this something Excel can handle? "Dave Peterson" wrote: If you try something like this, I think you're setting yourself up for failure. Way too many things can go wrong. I would take a different approach. Put all your data in one worksheet (the parent worksheet). Do all your data entry there. If you need to view different "slices" of your data, maybe you can use data|filter|autofilter to view the stuff you want to see. If you absolutely must have multiple sheets, then I would still keep the data in one worksheet (and update it there), but use a macro to split the parent's data into each of the child worksheets. You may want to look at the way Ron de Bruin and Debra Dalgleish approached it: Ron de Bruin's EasyFilter addin: http://www.rondebruin.nl/easyfilter.htm Code from Debra Dalgleish's site: http://www.contextures.com/excelfiles.html Create New Sheets from Filtered List -- uses an Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.xls 35 kb Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- replaces old data with current. AdvFilterCity.xls 55 kb If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm JasonG wrote: Thank you in advance to anyone who attempts to answer this post. I have a total of 6 excel spreadsheets that I would like to link to each other. 5 of them could be considered 'child' sheets and one 'parent' or master sheet. On the 5 child sheets, each will contain text and numbers and a sum at the end. On the parent sheet I would like to have 6 tabs. One tab for each child sheet that is a constantly updating copy of changes made in child sheet. After those 5 tabs I would like to have a 6th tab that is a grand total of the previous 5 tabs. It should contain copies of all the other 5 tabs, one on top of the other, and then a sum total at the end. I would then like to have a copy of that 'total tab' on 2nd tab on each of the child spread sheets. Is something like this possible? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking Cells from different worksheets
If you want simultaneous updates, then maybe excel is not the right application
for you. You could use a shared workbook, but lots of knowledgeable people stay away from them because of all the limitations (and possible corruption that comes with it). If you wanted, you could keep 5 different workbooks/worksheets and then create a new worksheet based on those. Essentially, you'd open each and copy|paste to a new workbook each time you wanted to get that summary file. Ron de Bruin shares lots of sampe code he http://www.rondebruin.nl/tips.htm (Maybe using a real database program (like Access) would be better????) JasonG wrote: Thanks for the insight Dave. The reason I wanted 5 different 'child' spreadsheets is because we have 5 different people that would be updating these sheets through out the day. Unless I am mistaken, if one person has the master sheet open then the other people can not make changes? Is this something Excel can handle? "Dave Peterson" wrote: If you try something like this, I think you're setting yourself up for failure. Way too many things can go wrong. I would take a different approach. Put all your data in one worksheet (the parent worksheet). Do all your data entry there. If you need to view different "slices" of your data, maybe you can use data|filter|autofilter to view the stuff you want to see. If you absolutely must have multiple sheets, then I would still keep the data in one worksheet (and update it there), but use a macro to split the parent's data into each of the child worksheets. You may want to look at the way Ron de Bruin and Debra Dalgleish approached it: Ron de Bruin's EasyFilter addin: http://www.rondebruin.nl/easyfilter.htm Code from Debra Dalgleish's site: http://www.contextures.com/excelfiles.html Create New Sheets from Filtered List -- uses an Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.xls 35 kb Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- replaces old data with current. AdvFilterCity.xls 55 kb If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm JasonG wrote: Thank you in advance to anyone who attempts to answer this post. I have a total of 6 excel spreadsheets that I would like to link to each other. 5 of them could be considered 'child' sheets and one 'parent' or master sheet. On the 5 child sheets, each will contain text and numbers and a sum at the end. On the parent sheet I would like to have 6 tabs. One tab for each child sheet that is a constantly updating copy of changes made in child sheet. After those 5 tabs I would like to have a 6th tab that is a grand total of the previous 5 tabs. It should contain copies of all the other 5 tabs, one on top of the other, and then a sum total at the end. I would then like to have a copy of that 'total tab' on 2nd tab on each of the child spread sheets. Is something like this possible? -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking Cells from different worksheets
I agree completely. Thank you Dave
"Dave Peterson" wrote: If you want simultaneous updates, then maybe excel is not the right application for you. You could use a shared workbook, but lots of knowledgeable people stay away from them because of all the limitations (and possible corruption that comes with it). If you wanted, you could keep 5 different workbooks/worksheets and then create a new worksheet based on those. Essentially, you'd open each and copy|paste to a new workbook each time you wanted to get that summary file. Ron de Bruin shares lots of sampe code he http://www.rondebruin.nl/tips.htm (Maybe using a real database program (like Access) would be better????) JasonG wrote: Thanks for the insight Dave. The reason I wanted 5 different 'child' spreadsheets is because we have 5 different people that would be updating these sheets through out the day. Unless I am mistaken, if one person has the master sheet open then the other people can not make changes? Is this something Excel can handle? "Dave Peterson" wrote: If you try something like this, I think you're setting yourself up for failure. Way too many things can go wrong. I would take a different approach. Put all your data in one worksheet (the parent worksheet). Do all your data entry there. If you need to view different "slices" of your data, maybe you can use data|filter|autofilter to view the stuff you want to see. If you absolutely must have multiple sheets, then I would still keep the data in one worksheet (and update it there), but use a macro to split the parent's data into each of the child worksheets. You may want to look at the way Ron de Bruin and Debra Dalgleish approached it: Ron de Bruin's EasyFilter addin: http://www.rondebruin.nl/easyfilter.htm Code from Debra Dalgleish's site: http://www.contextures.com/excelfiles.html Create New Sheets from Filtered List -- uses an Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.xls 35 kb Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- replaces old data with current. AdvFilterCity.xls 55 kb If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm JasonG wrote: Thank you in advance to anyone who attempts to answer this post. I have a total of 6 excel spreadsheets that I would like to link to each other. 5 of them could be considered 'child' sheets and one 'parent' or master sheet. On the 5 child sheets, each will contain text and numbers and a sum at the end. On the parent sheet I would like to have 6 tabs. One tab for each child sheet that is a constantly updating copy of changes made in child sheet. After those 5 tabs I would like to have a 6th tab that is a grand total of the previous 5 tabs. It should contain copies of all the other 5 tabs, one on top of the other, and then a sum total at the end. I would then like to have a copy of that 'total tab' on 2nd tab on each of the child spread sheets. Is something like this possible? -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking Cells from different worksheets
Hi Dave,
I took your advice and decided to create on master sheet. I tried using one of the sample spreadsheets from Debra Dalgleish's site. It's the "Update Sheets From Master" sample. I used this macro in my spreadsheets and made a few minor adjustments to make it match my worsheets and it works very nicely. I really appreciate your help. The only problem I am running into is that her sample only copies the first 13 Columns (A-N). Could you tell me how I could change the code to copy all coulmns on to the other weeksheets? Also, it seems to not want to copy cells that have dates, for example 5/10/2007. Could it be the format or is there something about the third column? This is all very interesting stuff. I never knew excel had so much functionality when paried with VB. Thanks again for your assistance! "JasonG" wrote: Thanks for the insight Dave. The reason I wanted 5 different 'child' spreadsheets is because we have 5 different people that would be updating these sheets through out the day. Unless I am mistaken, if one person has the master sheet open then the other people can not make changes? Is this something Excel can handle? "Dave Peterson" wrote: If you try something like this, I think you're setting yourself up for failure. Way too many things can go wrong. I would take a different approach. Put all your data in one worksheet (the parent worksheet). Do all your data entry there. If you need to view different "slices" of your data, maybe you can use data|filter|autofilter to view the stuff you want to see. If you absolutely must have multiple sheets, then I would still keep the data in one worksheet (and update it there), but use a macro to split the parent's data into each of the child worksheets. You may want to look at the way Ron de Bruin and Debra Dalgleish approached it: Ron de Bruin's EasyFilter addin: http://www.rondebruin.nl/easyfilter.htm Code from Debra Dalgleish's site: http://www.contextures.com/excelfiles.html Create New Sheets from Filtered List -- uses an Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.xls 35 kb Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- replaces old data with current. AdvFilterCity.xls 55 kb If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm JasonG wrote: Thank you in advance to anyone who attempts to answer this post. I have a total of 6 excel spreadsheets that I would like to link to each other. 5 of them could be considered 'child' sheets and one 'parent' or master sheet. On the 5 child sheets, each will contain text and numbers and a sum at the end. On the parent sheet I would like to have 6 tabs. One tab for each child sheet that is a constantly updating copy of changes made in child sheet. After those 5 tabs I would like to have a 6th tab that is a grand total of the previous 5 tabs. It should contain copies of all the other 5 tabs, one on top of the other, and then a sum total at the end. I would then like to have a copy of that 'total tab' on 2nd tab on each of the child spread sheets. Is something like this possible? -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linking Cells from different worksheets
#1. My first guess is that when you defined the range DataBase, you didn't
include all the columns you needed. If you really want all 256 (16384???) columns copied, then include all those columns in the range name. #2. I just ran Debra's code and she has dates in it that were copied ok. I don't have a guess why you're not getting those rows with dates copied. JasonG wrote: Hi Dave, I took your advice and decided to create on master sheet. I tried using one of the sample spreadsheets from Debra Dalgleish's site. It's the "Update Sheets From Master" sample. I used this macro in my spreadsheets and made a few minor adjustments to make it match my worsheets and it works very nicely. I really appreciate your help. The only problem I am running into is that her sample only copies the first 13 Columns (A-N). Could you tell me how I could change the code to copy all coulmns on to the other weeksheets? Also, it seems to not want to copy cells that have dates, for example 5/10/2007. Could it be the format or is there something about the third column? This is all very interesting stuff. I never knew excel had so much functionality when paried with VB. Thanks again for your assistance! "JasonG" wrote: Thanks for the insight Dave. The reason I wanted 5 different 'child' spreadsheets is because we have 5 different people that would be updating these sheets through out the day. Unless I am mistaken, if one person has the master sheet open then the other people can not make changes? Is this something Excel can handle? "Dave Peterson" wrote: If you try something like this, I think you're setting yourself up for failure. Way too many things can go wrong. I would take a different approach. Put all your data in one worksheet (the parent worksheet). Do all your data entry there. If you need to view different "slices" of your data, maybe you can use data|filter|autofilter to view the stuff you want to see. If you absolutely must have multiple sheets, then I would still keep the data in one worksheet (and update it there), but use a macro to split the parent's data into each of the child worksheets. You may want to look at the way Ron de Bruin and Debra Dalgleish approached it: Ron de Bruin's EasyFilter addin: http://www.rondebruin.nl/easyfilter.htm Code from Debra Dalgleish's site: http://www.contextures.com/excelfiles.html Create New Sheets from Filtered List -- uses an Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.xls 35 kb Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- replaces old data with current. AdvFilterCity.xls 55 kb If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm JasonG wrote: Thank you in advance to anyone who attempts to answer this post. I have a total of 6 excel spreadsheets that I would like to link to each other. 5 of them could be considered 'child' sheets and one 'parent' or master sheet. On the 5 child sheets, each will contain text and numbers and a sum at the end. On the parent sheet I would like to have 6 tabs. One tab for each child sheet that is a constantly updating copy of changes made in child sheet. After those 5 tabs I would like to have a 6th tab that is a grand total of the previous 5 tabs. It should contain copies of all the other 5 tabs, one on top of the other, and then a sum total at the end. I would then like to have a copy of that 'total tab' on 2nd tab on each of the child spread sheets. Is something like this possible? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Cells on different worksheets | Excel Discussion (Misc queries) | |||
linking cells between worksheets | Excel Discussion (Misc queries) | |||
formulas for linking cells in different worksheets | Excel Worksheet Functions | |||
Linking changing values (cells) between worksheets. | Excel Worksheet Functions | |||
Linking cells in a worksheet to other worksheets in a workbook | Excel Discussion (Misc queries) |