Combine different versions of same excel worksheet into one

I have an excel worksheet that has 2500+ contacts w/ address.  This list gets reviewed by roughly 17 different people in order for them to select the names they want to invite to an event or to receive a holiday card.  So my excel sheet has 3 columns A,B,C, where the user needs to mark if they want a contact in 3 different categories.  Say they use their initials to mark, when i combine 17 of these worksheets into one, i want the columns A,B,C to have multiple data, i.e, (worksheet 1 initials, worksheet 2 initials, etc.).

My guess is i need to import this data into a sql database or access - i would prefer sql. Once i have an updated database with all this info i can take it from there and put it back into excel.

Any guidance on the sql script is appreciated. Also, if this can actually be done in excel, that would be great but i'm doubtful.

I just had a thought of adding all the sheets into one workbook and making a master sheet that combines all the worksheets#.columnA and so on - however i would need to make sure all these sheets are in the same order.  it is possible that some users may add or delete contacts from the initial 2500+ (but i might just lock them out of doing that)
Who is Participating?
David L. HansenConnect With a Mentor Programmer AnalystCommented:
You could also just put all the Excel files in one folder and then in the "Totals.xlsx" workbook you could have formulas which refer to ranges in those other files (but this way you can't use named-ranges, just cell-based naming with this approach..see here).  This, however, would still allow you to open a single spreadsheet and have it pull data from other Excel files without ever having to open them.  By the way, Windows 7 and 8 have higher security than XP did and so you may have to deal with that (by opening Excel as admin or by lowering the UAC).
David L. HansenProgrammer AnalystCommented:
This could be build in LightSwitch so easily!  MS LightSwitch is like the old MS Access except it is far more reliable and can be ported to the cloud in a snap (if you choose).  You don't need to be a programmer or database guru to use it either.  I could show you the tables you'd need to build and how they relate then you could take it from there.

The best part of this approach is that you just put it in the cloud and send out emails to your reviewers.  They click the link in the email and are taken to your program (web-based).  They do their reviews and never need to see other reviewer's choices.

What do you think?
David L. HansenProgrammer AnalystCommented:
The tables are built by LightSwitch as just need to give it the names of the fields (and type) used in each table and how the tables relate (all of which I can give you).
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

LB2015Author Commented:
lightswitch might be a fine solution, but i think for this quick project i'd like to keep everything excel or sql.
David L. HansenProgrammer AnalystCommented:
You can use "named-ranges" to accomplish this.  It is not very elegant, but should do the job.  I don't recommend using Excel in this way (asking it to act like an complete front-end/back-end app) but if you want to put in the time to build it and maintain it (maintaining is what becomes the headache) then by all means.  Best of luck with it. :)

Here's what you do:
1. Create a "Totals" sheet
2. Create a "Candidates" sheet
3. Create a "Reviewer-Tom" sheet
4. Create a "Reviewer-Jane" sheet
5. On the Candidates sheet create a column of Name (you could use make First and Last if you want....even address if you want)
6. Fill in some candidate names
7. Select all the data (with column headers) you just put in (not the whole sheet) and give it a name of "CandidateList" in the named-range textbox (it's exactly above Column A).
8.  In the Reviewer-Tom sheet goto A1 and type "=CandidateList" and hit enter.  Continue to cell B1, etc. until you have all the data showing in row 1 that you want.  Then, pull the formulas down thus filling the other rows (You may see zeros where there is no data to show)
9. Go to the next column over (say it is column D) in cell D1 type "Choice-Tom"
10. Select column D (the "Choice-Tom" column) and give it a named-range of "TomDecision"
11. Do the same sort of setup in the "Reviewer-Jane" sheet BUT put her "Choice-Jane" column (don't forget to give it a named range of "JaneDecision") in the E column instead of D (just trust me).
12. Now in the "Totals" sheet get the candidate info the same way you did on the reviewers' sheets
13. Pull in each Reviewers choices using their named ranges (should be column D and E)
14. Add totals in column F (note this will move as you add new reviewers) for each candidate row.  If the reviewers use "Y", "N", "M" (m is for maybe) then you can use the "CountIF" function to get a total for Yes's, No's, and Maybe's (each in their own totals column).
LB2015Author Commented:
I will want all 17 users to be able to work on this at the same time if it so happens, so I need to keep each users contact list in a separate workbook. After everyone is done reviewing, i can combine into one workbook with 17 sheets with an 18th sheet being the master sheet with formulas to combine all the other sheets.  I just need to be able to guarantee that this formulas match up the right columns, either by a unique id column that is identical throughout or maybe by verifying first name and last name is identical in all 17 sheets.
David L. HansenProgrammer AnalystCommented:
I would lock the candidate sheet so that nobody can change the data there.  Everyone will have a candidate sheet in their workbook.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.