Update master page from other tabs in Excel 2007
Hi, I have an excel spreadsheet with 6 tabs. I've created a seventh tab called Master. They all have the same headings and columns.
I'd like the master tab to contain everything from all the other tabs, and also to update when another row is added to another tab.
So if some adds a new row to tab one, a new row will be created in the Master tab and the identical data will be input.
I tried this: http://smallbusiness.chron.com/insert-data-multiple-excel-spreadsheets-one-excel-spreadsheet-75667.html and it didn't work, it added the headers but then just some random info, mostly the zip codes. The few phone numbers that were added to the master sheet weren't formatted correctly, and reformatting the cells before consolidating didn't help that problem.
Can someone help? Thanks!
tabs are named:
Snr Nutrition, Hosp, Snt Ctrs, Renal Diet, HomeCare, Rehab, Master -New. They can be renamed (like Renal_Diet) if it's easier.
I'd like the master tab to contain everything from all the other tabs, and also to update when another row is added to another tab.
So if some adds a new row to tab one, a new row will be created in the Master tab and the identical data will be input.
I tried this: http://smallbusiness.chron.com/insert-data-multiple-excel-spreadsheets-one-excel-spreadsheet-75667.html and it didn't work, it added the headers but then just some random info, mostly the zip codes. The few phone numbers that were added to the master sheet weren't formatted correctly, and reformatting the cells before consolidating didn't help that problem.
Can someone help? Thanks!
tabs are named:
Snr Nutrition, Hosp, Snt Ctrs, Renal Diet, HomeCare, Rehab, Master -New. They can be renamed (like Renal_Diet) if it's easier.
Martin Liss
A sample workbook would help.
ASKER
ok, then.
CURRENT-Special-Diet-list.xlsx
CURRENT-Special-Diet-list.xlsx
A few questions:
1) Do you want the Master sheet to show the "Activity" data?
2) If the user changes a cell in one of the non-Master tabs, do you want that change to show up in the Master?
3) If the answer to 2 is "yes" how do I know where the data fora particular Master row came from? Is the data on each non-Master tab unique? I suppose I could just clear the Master tab any time a change is made to any non-Master tab and repopulate the Master tab from top to bottom.
1) Do you want the Master sheet to show the "Activity" data?
2) If the user changes a cell in one of the non-Master tabs, do you want that change to show up in the Master?
3) If the answer to 2 is "yes" how do I know where the data fora particular Master row came from? Is the data on each non-Master tab unique? I suppose I could just clear the Master tab any time a change is made to any non-Master tab and repopulate the Master tab from top to bottom.
I have seen similar solutions but with the movement of data going the other way.
The Master sheet is updated manually and then a routine runs to populate/create individual tabs based on a filter value from the Master.
The Master sheet is updated manually and then a routine runs to populate/create individual tabs based on a filter value from the Master.
ASKER
Hi, Martin,
I would say yes to both questions, although the change to any cell would be rare. Thanks for looking at this.
I would say yes to both questions, although the change to any cell would be rare. Thanks for looking at this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You are literally a genius. Thanks so much.
You're welcome and I'm glad I was able to help, and that you for the compliment.
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015