# Excel INPUT Sheet???



## allenkc (Feb 6, 2003)

Is it possible to make an input sheet in a workbook that will basically allow me to type in data into the provided cells and then hit enter and it add that data to a totals sheet in the workbook? After you hit enter the data fields become clear and you can put in another round of data. 

I am not sure I explained that correctly but if you have an idea of what i am talking about please let me know how to do this or at least point me in the right direction.

Thanks for your time!!


----------



## Rollin_Again (Sep 4, 2003)

You can do this with a VBA Macro. Can you post your sample workbook and we'll assist with the code. You can also create a custom form to enter you data into instead of entering your values directly on the sheet. I've attached two sample workbooks showing how this can be done. One of the sample uses a custom form to enter your data into and the other allow you to enter your data directly into the first worksheet. You will need to enable macros in Excel for these to work. The code can be trigger from the macro list or a button click.



Regards,
Rollin


----------



## allenkc (Feb 6, 2003)

thanks rollin for the offer. I will not have access to my file until tomorrow at work. I will post an edited version and explain how i would like it to work. Again thanks for your help.


----------



## Anne Troy (Feb 14, 1999)

Hi, Allen. In a file that contains your current data, or at very least your column headings, consider Data-->Form. That automatically creates an input form for your data. It's great if you don't have a gazillion fields.


----------



## allenkc (Feb 6, 2003)

Thanks Anne Troy for the idea but that is not exactly what i need. I said I would post a sample of what I am working with. 

This is what I would like to have. I would like an input box that would allow me to:

1. type a persons name
2. based on that persons name is where the following numbers would be added to
3. the numbers fields include, 1st up, Be-back, demo, worksheet, 1st sold, be-back sold, desk control and delivery. Once I hit enter or a button then it will add those numbers to the numbers already on the totals page based on the persons name. So if Alex already shows 3 1st ups and I input on this page, 2 1st ups then the total should change to 5.
4. So in this input box there should be 9 boxes.

I hope that makes sense. If it doesn't please let me know. Thanks again for your help!!


----------



## The Villan (Feb 20, 2006)

Allen
I have had a quick look at your spreadsheet.

Firstly, how often are you inputting data (Monthly, Daily etc)
Are these year to date figures you are providing or per Month
Are you doing a sort of Managment Accounting type job here?

The simple way as I would see it is to create a sheet for each person and name the tabs accordingly.
Dependant on how many inputs you do would depend on how you lay out those raw data sheets.
I would be having a total column on each sheet totalling up the data you input.
I would then have a main sheet similar to what you have uploaded and the cells in that sheet would have formulas picking up the totals from each persons sheet.

So all you would be doing is go to a persons raw data sheet and input some more data and the totals would update immediately and so would the results in the main sheet.

See the example I have uploaded.

Be careful there are formulas in the total sheet and formulas in Alex and Daniel sheets. I have indicated those by making the background of the cell light Blueish.Do not type in these.

Go into the Alex sheet and input some numbers into the March column.
Notice that column N updates automatically.
Check the Total sheet and the numbers for Alex should have updated.

So my question is - Is that the sort of thing you are trying to do?


----------



## allenkc (Feb 6, 2003)

Thanks Villan!!!

I guess me and you are on the same wavelength or something because that is what I am already using. I thought that the single sheet or "input box" would be simpler when it comes to inputting this data and besides the managers only want to see the monthly totals anyway.

To answer your questions:

I am inputting this data on a daily basis.

The totals are provided on a monthly basis.

Yes this is a management accounting type job.

Again thanks for your help on this!!!


----------



## bomb #21 (Jul 1, 2005)

"Once I hit enter or a button", etc. is crying out for a macro, AKA VBA. But in this case it's not recommended because ... if you're adding the inputs to "figures to date" and you make even one input error, there's no audit trail.

If inputting is on a daily basis and "they" only want to see the monthly totals, Villan can easily knock you up a pivot table set-up -- and probably will. 

(hi Les  )


----------



## The Villan (Feb 20, 2006)

Hi Bomb  You alright.
>>there's no audit trail<< and that is the most critical thing here.

It is far safer to have a page for each person and input on a daily basis and always be in a position to provide mangement with bang up to date info, that is accurate and can be easily checked, if there is suspicion of data error input. It certainly takes the pressure off at the month end. In that situation, daily input shouldn't be more than a 10/15 min job per day.

Not sure if a pivot table is going to be the answer Bomb as i suspect that Allen needs to provide a standard management Report.

I am sure I can do something for you Allen. However I will need to know exactly what your bosses are asking you to fill in. Obviously that spreadsheet could have sensitive data on it, which might have to be changed around.

From that i can see if there are any ways to improve your job in relation to data input and the report that you have to provide.

If you want me to help Allen, then I suggest you pm me and to begin with we work by e-mail. What I normally do, is if we solve it, it gets posted up here so that others can see how it has been solved. If I get so far and then need people like Bomb or Firetyr to finish off we put it back up here for them to finish off.

So Pm me if you would like to carry on.


----------



## Zack Barresse (Jul 25, 2004)

Reading this I immediately think 'database'. Second I think 'pivot table'. Raw input should not be pretty, but it should be normalized. After that, let your output be in a separate sheet, and I would recommend looking at pivot tables. Of course, Access has nice reporting capabilities if you have normalized data as well. Just at a quick glance..


----------



## The Villan (Feb 20, 2006)

If you are involved in management Accounting, you will realise that man reports tend to have a fixed approach to them and generally thats what the directors want to see at their meetings.
So although Pivot table may be useful, they do not provide the mangement report required.
In this case Access is a similar problem. It doesn't conform to what managment wants at their meetings.


----------

