Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need Help Entering Data For Personnel in Excel Workbook

Posted on 2016-09-21
12
Medium Priority
?
110 Views
Last Modified: 2016-09-23
I want to create a spreadsheet that only has a few drop-down boxes or text fields where a person can select their name from the list, enter their data and have it automatically added to the sheet that has their name on it.

We have an Excel workbook that has several spreadsheets with different people's names listed.

Next to their names are columns for people to enter their scores (i.e.- pull-ups).
We are having a contest on who can do the most in a month! :-)

Since we're going to post this on our SharePoint, I want to make it look as easy as possible for everyone.

See the example sheet attached to this post and feel free to provide changes to the existing structure.
EXAMPLE-PULL-UP-LOG.xls

It would also be a big bonus if you can show me how to also put a chart of the top 10 scores on the first sheet as well!
Thanks, EE community!
0
Comment
Question by:Paul Wagner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 11

Expert Comment

by:Craig Yellick
ID: 41809168
SharePoint has a Survey list template where you define questions that can include drop-down lists and check boxes, etc. The responses are stored to an Excel spreadsheet.

Create a Survey in SharePoint

Once you have an Excel file stored in a SharePoint list it's a simple matter to create a page with an Excel webpart that displays top-10 information, pie/bar charts, whatever else you might want to show.
0
 
LVL 5

Author Comment

by:Paul Wagner
ID: 41809584
We need everything to "live" within the spreadsheet. Using SharePoint, other than for uploading the doc, is not an option.
0
 
LVL 11

Expert Comment

by:Craig Yellick
ID: 41809616
100% of the data in a SharePoint survey is stored in an Excel spreadsheet so from that perspective, it's still all Excel and all in a file. The main benefits of the survey are:

  • Multiple user support
  • Sophisticated input controls and validation
  • Built-in roll-up capabilities

If you use a single Excel spreadsheet as you've done in your example, only one person at a time can make a change. If two or more people make parallel changes you'll have to merge the changes. While you can add validation and drop-down list controls to Excel, it's a lot of work that is done for you almost effortlessly with the Survey.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 5

Author Comment

by:Paul Wagner
ID: 41809648
I don't have access to change/add anything on the SharePoint. Everything needs to exist in the file.

Note: Would making an Access file work better in this case? I could make a form to enter the data with.
0
 
LVL 11

Expert Comment

by:Craig Yellick
ID: 41809658
Too bad about SharePoint, that survey feature is pretty much designed for these kind of uses. You mentioned "Since we're going to post this on our SharePoint", so I immediately saw that as a possible alternative.

An Access data entry form would be way more solid than passing an Excel file around. In addition to built-in multi-user support, It has the charting capabilities you want as well. Can also export to Excel if you want to use the more sophisticated features of Excel.

Only downside is that your users need to have access to a common file share on your network, either physically in the same office or through a VPN.
0
 
LVL 5

Author Comment

by:Paul Wagner
ID: 41809664
How would I take the example I attached and make a form for that in Access?
0
 
LVL 11

Accepted Solution

by:
Craig Yellick earned 2000 total points
ID: 41809681
Your spreadsheet structure is in what database designers call a "denormalized" form, which is typical for spreadsheets and their aggressively row-and-column format. While you could design a table that has fields for each day of the month, you would quickly wish you did not do that when it came time to analyze the data.

The central data table should be one that stores exactly one data point per record. That gives you maximum flexibility for using the data in different ways.

  • PersonID
  • ChallengeID
  • Date
  • Quantity

A separate table contains a list of People, which is related to the PersonID field. A separate Challenge table contains the list of challenges, which is related to the ChallengeID field.

Queries like the following can be used to summarize challenge data by person and period.  Or, ignoring people and summarizing only by a date or range of dates (e.g., total pull-ups done last Friday). Or ignoring challenges and summarizing by people across all challenges. Pretty much any question can be answered when the main data table holds a single fact.

select p.Name, c.Description, Reps = sum(Quantity)
from Data as d
inner join Person as p on p.PersonID = d.PersonID
inner join Challenge as c on c.ChallengeID = d.ChallengeID
group by  p.Name, c.Description

Open in new window

0
 
LVL 23

Expert Comment

by:Danny Child
ID: 41811027
I think you've uploaded your sample file as a .DOC, when it should be an .XLS
I renamed it as I saved it, and it opens ok that way.

I'm not quite sure why you want to use all the data box tools, they seem too complex when all the users have to do is enter a value in a cell?

I had mocked up an example before I figured out your file extn change as above.
Any good?
M--Personal-ee---pull-up-chart.xlsx
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 41811041
By the way, if you're using it for running, everyone is going to have to be careful on the format
Excel's keen on it being hh:mm:ss

Entering just
1:
sets it to be 1 hour.

Entering
1:1
equates to 1 hour, and 1 minute

see here for more info
https://office-watch.com/2015/entering-time-durations-in-excel/

You'll have to be especially careful here if these are being totalled.
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 41811047
By the way, the AutoFilter on the name column has the option for Top 10 (or 5, or whatever), and if you select this, the charts flip to show those results only.
0
 
LVL 5

Author Comment

by:Paul Wagner
ID: 41813092
@CraigYellick

While you could design a table that has fields for each day of the month, you would quickly wish you did not do that when it came time to analyze the data.

Part of the contest's intent is to see what people are doing each day.

I think I'm going to have to pay someone to do this. I don't know much about Access and feel that it wouldn't look/work in Excel.
0
 
LVL 11

Expert Comment

by:Craig Yellick
ID: 41813099
"Seeing what people are doing each day" is just one of potentially dozens of ways of viewing the data. If you structure the data tables correctly you'll be able to create reports and charts for any conceivable purpose. If you design the table so it looks like a report with days of the month along the top and people's names in rows, it will be very-very difficult to use the data for any other purpose.

As Access apps go it isn't all that complex, though the pivot queries you need for reporting will involve more advanced skills.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question