Excel Buttton Query

Hello,

what do you want the button to do? Return the number of times it was clicked? I'm not sure that I understand this.

From your data source, you can easily build a pivot table and use slicers to filter the data by date, sales and other. Slicers are like buttons. If you need help with this, please reply.

Which row on the data sheet should the button write to ?

What happens if the user clicks the button by mistake? (this will happen)

Using a button to increment a value in a sheet is very bad practice.

Please step back from the button scenario. Explain the bigger picture. What do you want to achieve? Do you want to enter the number of Sales calls and "other" calls in the data sheet? Why not let the user type in the number in the correct row? What is the purpose of the button? What does it achieve automatically that the user cannot be trusted to do manually?

So the sales rep made 113 sales calls. Does s/he need to click the button 113 times to log that?

The report is being set up to count how many sales calls are coming in rather than "other", which can be varying. The purpose of the button is to count either one or the other.

Asking a team of 20+ people to click an option a or option b is just easier than asking them to fill in a number or count (not a trust issue, just trying to simplify the process for the user).

Replying to your specific questions

- user clicks "Sales Call" - it adds 1 to B2 on the Data tab, if they click Other Call, it adds 1 to C2 (all for the current date)
- If a mistake is made, possibly will add an additional two buttons which are the reverse of the addition buttons
- I appreciate that this isn't the best method to capture this type of information, but I just need a quick low tech solution to give me a general view of the volume of calls coming through to a sales dept that are NOT sales related. (the phone system has IVR's etc, but customers will select whatever options to speak to someone rather than an automated option)
- If there were 113 sales call, yes, the sales person would need to click the button 113 throughout the day

Any help is appreciated as I don't want someone keying in a number

A quick, low tech solution would be a piece of paper and a pencil, so the sales person can keep a tally.

For an Excel solution I suggest that the data architecture be slightly different than what you describe. The data tab should contain a table with two columns: Date and Call Type. Each click on a button will create a new entry in the table. For a Sales call the call type will be set to "Sales", for other calls the cell will be set to "Other". The date cell will contain the date and time that the button was clicked.

This way, it will be much easier to identify if a the button may have been clicked several times by mistake. Individual clicks create individual rows and these can be deleted individually.

To tally up the data, you can create a pivot table (and a pivot chart) where the calls are added up.

So, there are two buttons (shapes). They call two macros. Each of the macros calls another macro that does the data entry. Here is the code:

Please Login or Register  to view this content.

Attached is a file with this suggestion built. It also contains a pivot table and chart to analyse the data.

And below the two buttons you can find a formula that counts the entries from the data table for the current day. It also gives the user a visual indicator that something has happened, because the numbers will change with each click.

Since I live in New Zealand, my "today" is already Nov-27, so don't get confused by those dates when you open the file and you're still on Nov-26.

Gee, thanks. Your location has inspired me to go the extra mile because I have fond memories of motorbiking the Wicklow Mountains and surrounds. That was before the dinosaurs, though.