Input tab

[jmcole]

I have employees that work on different cash registers each day. I want to create a workbook that will track their shortages.

I have a workbook with 12 tabs, one for each month. On the monthly tabs, column A has all of my employee numbers. The rest of the columns are each day of the month. I want to make an Input Tab. On the Input Tab, I want to be able to type in the Employee Number, the Month, the Day, and the Amount, then click a submit button. I want that information to automatically be inputed on the correct tab (Month) and in the correct column (Employee Number and Day). Also, if the employee number is already in the column, I want it to automatically add the additional shortage for that employee to the amount for that day for that employee.

Any help would be greatly appreciated.

[shg]

You could instead put all the data on a single tab -- name, date, shortage amount -- and then use a pivot table to summarize by employee, month, ...

[jmcole]

Well, each monthly tab is tied into a graph and red flags pop up when a daily amount exceeds a certain amount. I would really hate to create the whole workbook again.

[jmcole]

I have attached an example file of what I wish to do.

[shg]

I would really hate to create the whole workbook again.

I promise it would be a worthwhile exercise to stop fighting Excel. Ask for some other opinions.

[FDibbins]

I agree with shg, it is always, without exception, better/easier to have all data on 1 sheet, and then summaries/analysis of that data on other sheets.

excel is designed to work that way, and although you can do it the way you want, it will entail far more effort and resources, and could even cause you to have to exclude some functions that might turn your workbook from a nice/good tool, into a great/awesome tool.

a little extra work up front will, i promise, pay dividends later

take a look at the attached. I added 2 sheets - Data and Emplyees. the summary on the emplyee sheet took me minutes to create, it's flexible, exoandable, and can be used for all sorts of other things

[jmcole]

First, let me say thank you for the help and the suggestions. This is the workbook that I have been creating for about a month. I'm far from being "good" at Excel, that's why it has taken me so long. I still want to try an create an input tab, I really don't want to start all over again.

[FDibbins]

I can understand that completely, all that work for nothing. but let me take a look at what you have, im sure shg will do the same, and lets see what we can suggest

[shg]

I looked, and stand by my suggestion. The workbook makes something simple overwrought and needlessly complex.

[oeldere]

I both agree SHG and FDibbins.

Get all data on 1 worksheet (instead of differant worksheets).

Use pivot table, it's made for that kind of work.

I fully understand, you don't want to throw away, a month of work.

But if you understand how an pivot table works, you will be very satified, you get the tip to work with it.

Added

See the example (I use the file of Fdibbins) to make an pivot table for you.

Do you have questions of comments, just ask or comment.

[jmcole]

Thank you all very much. I guess I'm trying to make something simple, complex. I will take the suggestions and make a new workbook.

[FDibbins]

dont feel bad, I have seen lots and lots of members here, begin making systems just like you did, and seen them get the same advice that you just received. it is a very common and understandable "newbie" mistake - you have all these sheets to play with, so lets play with them

another piece of advice I would offer (and it also links in to the work you have already done) is to keep it as simple as you can. Complex systems may look visually impressive, but more often than not, the maintenace of such systems can be a serious head-ache, and modifying or adding to them can be a night-mare

looking at your 2nd upload...

that is quite an impressive system you are constructing, I can totally understand your reluctance in "ditching" some of it.

what is your aim with the graphs/charts you have created on the ??Overview sheets? they go so far to the right, that only by dropping the sheet magnification to 10% can the entire chart be seen, and then it is too small to see anything anyway?

that file contains no actual data at all, everything is blank. I can understand if the file contains sensitive info.

[jmcole]

Yes it is sensative information, that's why I uploaded a blank workbook. Believe it or not, the information to the far right helps when investigating shortages. It is the total for the month for that operator. On the bottom, is the total for all operators for that day. The overview sheets show the operator shortages on a graph. Some people can understand a graph easier than looking at the numbers. The higher the graph (dollar amount) the more investigating needs to be done. The problem with what I have is, if a operator was on two or three registers that had a shortage for that day, you have to search for that operator number and add all three shortages. I was just trying to make the input of information easier.