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)
LB2015Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David L. HansenCEOCommented:
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?
0
David L. HansenCEOCommented:
The tables are built by LightSwitch as well...you 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).
0
LB2015Author Commented:
lightswitch might be a fine solution, but i think for this quick project i'd like to keep everything excel or sql.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

David L. HansenCEOCommented:
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).
0
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.
0
David L. HansenCEOCommented:
I would lock the candidate sheet so that nobody can change the data there.  Everyone will have a candidate sheet in their workbook.
0
David L. HansenCEOCommented:
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).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.