• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Collection using Excel...what is the best way?

PP3321

PP3321

Active Member
Each week,
I need to ask 3 of my colleagues to enter data.
I ask how many events attended during that week.

Final report should have 2 columns only.

How would experts achieve this with Excel?

I can think of 5 ways to do this but I need to find the easiest way to do it...

-UserForm with Macro?
-InfoPath?
-Excel Online?
-Excel in Shared Drive...?
-Data Collection by Email and compile 1 report manually?

Any advice is appreciated...



Screenshot.png
 
Here's an Excel example. Not Possible without 3 column or more as you need to show your colleague names
 

Attachments

  • Sample.xlsx
    33.7 KB · Views: 16
If I understand your question correctly, and speaking from a small bit of experience, the "UserForm with Macro" will be the easiest for you over time...especially if you have combined "Excel in Shared Drive" as well.
Once you have a satisfactory and working "UserForm with Macro," it's all downhill for you. You'll only have to check to make sure your colleagues have actually input their data.

Since you only have 3 data inputs (colleagues), it may be over-complicating things to create a "UserForm with Macro." Ultimately, you'll have to decide what makes the best sense for you.

I have been doing the "Data Collection by e-mail and compile one weekly report manually" for several years. ...mostly out of necessity and due to the complex nature of the data.

Report back on what you decide.
 
Thank you so much for this chirayu!!!

So the end-users will input data on Data Sheet (Column F and G)?
Then using countifs, automatically do calculations in Summary Sheet?
Is my understanding correct?

screenshot.png
 
Thank you Eloise,
I agree UseForm maybe easier in the long run.
We may increase the number of users also in the future...
 
Yes User's will input data in data sheet & countifs should work out the rest. Alternatively use a Pivot to do the job of the countifs. Also as Eloise said you can add a userform to do the job. So I suppose you could add a userform with calendar control which would add into the data sheet. I just put the entire month in as it was easier for me to make the sample data, but individual days would do.
 
Last edited:
You must log in or register to reply here.
Back
Top