Excel 2007 : Create an input out output application in Excel for selling water

Hi,

I posted the same question on another board but since it's more of a project and I'm not really in a situation (read: living in ramen noodles world..) where I can pay for a solution the topic got closed ( http://www.ozgrid.com/forum/showthread.php?t=145753 ).

So, I thought that maybe I could try to find someone here.

In Short:
My parents bought a water-bottle-refill store a few days ago and I was just there and saw that they are tracking everything on a piece of paper that they then have to add up and tie to the cash register - and getting any 'statistics' from the sales is near impossible and/or very hard to do.

So...I thought I was going to be a good daughter and see if I couldn't bring them into the modern days and get them a used laptop and set them up with something that would be easier for them to track - and USE the data they are collecting

The challenge:
I'm not very good with spreadsheets/access databases myself..so as I said I thought I'd see if there would be a helpful soul here that might be willing to help out.

What they are doing now:
They sell two kinds of water:
- 25cents per gallon, and
- 45cents per gallon.

People can pay either by cash - or a 'prepaid membership card'.

They have a piece of paper with basically four columns:
C25, C45, M25, M45
(Cash 25c, Cash45c, Membership25, Membership45)

If someone comes in and buys, say, 5 gallons of the 25cent wanter and pay in cash - they put a '5' in the column named 'C25'. End of the day the add up all the columns and see if it ties to the cash register.

The reason they 'break out' the 25, 45, cash and membership is to see what sells and how (do they need the membership, is the more expensive water in demand, etc).

The Challenge:
Input:
I would love something easy to use but that looks 'professional'
I'm not sure if one can make a 'front-end' or 'form' maybe where they would just pick the cash/member/25/45 with checkboxes (or something) and then type in the gallons.
Something that's easy to use and for people that can't read text tooo small

Output:
I think this is maybe the real challenge.
I would love to be able to help them by giving them tools that could show them for instance;
- the total for the day,
-the total for the week,
- totals BY say member/cash,
- totals BY cash or membership,
- any total for a certain day/time period you type in,
- Etc

Maybe in text..maybe in like pie charts..I am not sure

Is this something anyone out there have an idea about how to do (including the 'form-frontend')?

I can't really offer any money as compensation (unemployed and soon, if things doesn't look better working in my parents waterstore ) - but maybe there is something to say for good karma.

Anyone.....?

/Tiff

Hoping someone may be able to help me out

Create a table of data in another sheet. Look at the code in the Contacts Form example to save the data to the table at the end of each day.

You should be able to use the code in the example just change the cell references

Originally Posted by royUK

You should be able to use the code in the example just change the cell references

Roy thanks!

I tried out the s/sheet again today and got some new questions - but I'm making progress I think :


1) I tried adding a graph (see the new, attached s/sheet) that auto-updates when I enter values and that works.... but I can NOT seem to get it to show what I want: I want the pie chart to be very simple - just two 'slices' showing a) the total C25 sales (so a COUNT of two columns) and b) the total C45 sales (another COUNT of two columns). It's driving me crazy as my pie chart may tell you if you look at it
I tried making the input "=Input!COUNT($B$4:$B$35)" but that doesn't seem to want to work

So...I then tried to make the input fields the already counted values in the statistics box and I THINK that works when it comes to the data...but I now can't make the slices being called "25c" and one "45c" as there is now only one 'series'... How can I fix this?

2) I deleted a blank column in the middle of the entry grid and now the values in one column doesn't get blanked out when pressing save - and the totals in the data tab doesn't get updated anymore! - and I can't find where to correct this

Btw:
When I click the "Save" button it automatically moves the values to the Data-sheet (coooll!!!)

I've amended the code to work with the new sheet

Please Login or Register  to view this content.

I'm not sure what you mean with the graph, but I suggest that you start a new question about the graph in the Excel Charting Forum

Well done.

Post the graph question separately with the example workbook

Originally Posted by royUK

Well done.

Post the graph question separately with the example workbook

Thanks Roy but I think I'm ok with how it looks now (attached a new version).

Your help has been invaluable!!

Planning on showing what I'll come up with to my mom this weekend and I think she will be thrilled

I got two more questions but I'll create two separate posts and try to be more specific in them

I marked this thread as "SOLVED" as I'm taking those two questions to separate posts and I think I am on a GREAT way thanks to the answers here!!