Input data then clear cell but save to another cell..Is there a formula for this?!

Hi everyone

I'm not sure how to explain this so please be patient if I don't make sense....

So at work we have "Client Satisfaction Surveys" and are inputting all the surveys into a data base.
The last question on the survey is "Rate our survey on a scale of 1-10"

I want to be able to see the average rating for each employee.. Is there a way to put in the rating from one survey and have it automatically add it another cell and then have it clear the first cell?

Here is an example of what I would like to do:
Say we get 2 surveys with one rating of 5 and one rating of 10... If I input the 5 into cell Q4 and have it automatically put into cell R4 and then it gets deleted from Q4 but saved to R4... and then I can add the rating of 10 to Q4 and it automatically ads it to R4 and clears Q4 and totals R4 to an average of 7.5.

This way, with each new survey I can just input each rating to Q4 and have it cleared for the next rating and then have an average rating of all the surveys in R4... we do not need to keep track of how many ratings we are getting, just the average rating.

Please see the attached worksheet if you want to have an idea of what I am trying to do...

If this is possible could someone please help I would really appreciate it!!
Or if anyone has any ideas of an easier way to do this please let me know.

Thank you soo much!

Sorry I didn't thoroughly read the Forum Rules & Regulations prior to posting... so can people reply now?

You will need to use VBA.
Right click on the sheet tab and choose "View Code"
Copy this code into the window that opens and then close the window.

Please Login or Register  to view this content.

Test to see if it works.

Hi ChemistB,

First off thank you so much! It worked great... however I do have a couple more questions...

1: Each time I put a number into cell Q3 is there a way to keep track of what number I put in and how many times? Can we make 10 columns numbers 1-10 and each time I add a number to Q3, it keeps track of how many times that number is entered in the new columns?? For example, Say someone gets 2 ratings of 10 and 1 rating of 5, in the column numbered 10, a 2 appears and in the column numbers 5 a 1 appears.

2nd:

I have attached my new work sheet.. For each employee they have their own section and the code thing you gave me works great. however, right now for each survey I put in, I have to add the numbers myself. For example, for the question overall satisfaction If i have two surveys that answer yes, I put in the first with "1" and then when i get a 2nd survey with the same answer I delete the "1" and put in "2"

I'm wondering if you could give me a code to have the same thing happen for each question.. so that I can just put in "1" and in another cell the total appears.
So if you look at the excel sheet I have now attached, in Ana's section I would like to put in 1 under "Yes" and have the "1" be cleared and then added to cell C4.. and thenthe same for the No's. (So same concept as before, however just adding to the cell underneath, as we want to keep track of how many Yes's and how many No's)

And I would like this for each question/answer and for each staff member... Please let me know a code for just one or two of the sections and what to change/add in the code to have this done for each person if you can't do all of them.

I would REALLY appreciate it!! Please see the new attached excel sheet so that you know what I am talking about lol.

I assume you want B4 to update (+1) when you input into Q3? I'm not a VBA guru. I will ask someone else to assist.

Try this code for the sheet you gave me. Can you upload a sheet representing how you wanted the first part of your question?

Please Login or Register  to view this content.

Hi Jasmine

ChemistB has requested another set of eyes to look at your Thread.

I believe this Code in the attached accomplishes what you want...I don't believe it accomplishes what you need.

Please Login or Register  to view this content.