How I create a summary worksheet on multiple worksheets (which keeps growing)

[fred]

Hi all:


I was presented a problem and I'm not sure if excel is the right way to do.


Sheet 1: Summary Sheet

Sheet 2: Customer A with columns include multiple contact names, phones, email, departments, etc

Sheet 3: Customer B with similar columns

Sheet 4: Customer C with similar columns

Sheet 5: Customer D with similar columns


There are currently 7 worksheets for each of the client. And if new clinets are in the pipeline, new worksheets will be added manually and tab renamed accordingly.


Objective:


In the summary sheet, creates rows of contact names, phones, email, corp address, etc that will automatically look up the columns from sheet 1 and place it on the "Summary" sheet. When it hits the end of the line, Excel will look up Sheet 2 for the same columns and fill in the data on "Summary" sheet, then repeat the process looking up and filling in columns of data on customers 3, 4, 5, etc accordingly.


As a result, there will be summary worksheet of all the contacts on each of the individual worksheets.


My first response is to use MS Access. But then there is no MS Access in the office. What is the best way to do this in MS Excel???


Please help. Thank you very much.

 

[Hui]

Fred

If you adding the extra sheets manually


1. Can you enforce that everyones sheets are in the same format ie: Col A is Date, Col B is name etc


2. How often do you do this ?

If its 5 times a day it may be worth automating.

If it is once or twice a month, it may be quicker to just do it manually, or at least get the columns in the same format maybe that could be automated and then just copy/paste the new data to the end of the old sheet


I'm not a fan of automation for automations sake.

 

[Luke M]

Ron de Bruin's has several articles that might be helpful.


Create a summary worksheet from different workbooks (formulas with VBA)

http://www.rondebruin.nl/summary.htm


Merge cells from all or some worksheets into one Master worksheet

http://www.rondebruin.nl/copy2.htm

 

[fred]

Thanks guys! I solved the problem thinking of the solution in reverse term.


Instead of having to manually update the individual worksheet and run macros to pull data. I convince the guy to manually input the data onto the summary sheet.


Using the "if" function, links, index, match and vlookup learnt from this blog site I was able to have him duplicate individual worksheets.


So the stpes are like this. Manually enter customer X's contact on summary sheet. Duplicate say, customer A worksheet to customer X. Then change the name customer A to customer X in cell B1 on the new worksheet. the rest is automatically populated.


At the end, he still got his data on summary and individual worksheet. No duplicate manual labor and no need for MS Access or complicated macros.


Thanks for the inspiration!