Excel 97 help

angus1

I'm not sure if this is the place to ask this type of question but I thought it was worth a try.

I am working on an Excel 97 spreadsheet at work that is to record the results of a questionnaire exercise we undertook recently. What i want is to have in a cell a running total. So that whatever number is in it will be added to automatically. For example it will start with '0' and I just want to be able to type in '1' each time and it adds it up automatically.

Hope this makes sense. Hope someone can help.

Thanks

angus1

Thanks Toots

I just really want to keep a count.

For example to the question 'What area of the country have you come from' there are several answers eg North of England, South of England, Scotland etc and I have put these headings on the spreadsheet and under each of these headings want to keep a count of how many people came from those various areas.

So I sit with the pile of questionnaires and if someone came from North of England I put a '1' in the cell and then if someone else comes from the North of England I just want to put another '1' in that cell but I want it to add that '1' to the previous '1' so it keeps a running total as I go through all the questionnaires.

Hope that makes more sense. I think it must be quite simple, just can't think how to do it.

Thanks

Aspal

Surely you can just overwrite the 1 with a 2, then a 3, then a 4? Will be only slightly less monotonous than just typing the number 1 all the time! Have fun!!!

Wombat

How about setting up a button underneath each column? Click the button for the relevant column depending on the answers on the questionnaire.

http://www.mrexcel.com/tip068.shtml

tomstickland

You want a click-counter sort of thing.

Simplest way is to record a macro, then put a button on the page and assing the macro to that button.

Besterest way of doing it is to put button on with controls toolbar. Then make some code for the button_click event.

Sheet1.cells(1,1)=Sheet1.cells(1,1)+1

sort of thing.

Actually, you might be able to use my Casino Counter spreadsheet.
I'll put a link to it.
http://www.tomstickland.co.uk/mse/casino_counter.xls

angus1

Thanks Tom and Wombat, that sounds just the thing. Will try it tomorrow. Hope it's not beyond me though as haven't done buttons and macros before. Will follow the instructions...

Wombat

Or you might be able to adapt this free counter for your use:

http://performance-measurement.com/m/Counter.htm

Wombat

An alternative would be to set up your Excel sheet with column titles etc. and then right click the Excel icon to the left of "File" and click on "View Code".

When the code window opens, click on "View" and then on "Poject Explorer". In the pane that opens listing your worksheets, double left click the sheet you just set up and paste the following code into the blank pane that opens:

Private Sub Worksheet_BeforeRightClick(ByVal Target _
As Excel.Range, Cancel As Boolean)

Cells(Target.Row, Target.Column) = Cells(Target.Row, Target.Column).Value + 1

Cancel = True

End Sub

Save your Workbook and close the code window. You should now be back on your worksheet and now, every time you right click a cell, it will increment its value by 1.

Note: this is very rudimentary and doesn't check which cells are being clicked, so it will attempt to increment ANY cell you right click. Also, don't be too quick with your clicking! If you click repeatedly faster than about once per second, the macro won't register every click.

HTH.

angus1

Thanks Wombat, that works great.

:j

Wombat

No problem. Glad it fits the bill.

Incidentally, if you happen to make a mistake just left click the offending cell and type in the correct value.

Stompa

Another variation here:

http://www.pcreview.co.uk/forums/thread-1055395.php