Hello Anita
This is an interesting problem you are facing but I think you are correct when you said you feel a spreadsheet should be able to solve it. Before I say anything else, I had to laugh when you were talking about your boss and said, "He rambles off a bunch of things so I am basically trying to decipher what it is he really wants ..." Anita, I know exactly what you are going through and the feeling of almost despair, when this happens.
My boss and yours must be related, because mine will do the same thing. He'll hand me a scrap piece of paper or an old envelope with stuff scribbled on the back, and say, "Can you make this?" and then he leaves. I'm left trying to decipher his writing, then like you, attempting to figure out exactly what he wants and how am I going to do it. It's nice to know I'm not the only person that has to deal with things like this. If anyone on this site is a boss/manager, please have mercy on your employees.
I agree with you when you said AhoyNC put a great deal of thought into coming up with the solution being presented. Good job, AhoyNC! There are a couple of concerns and suggestions I'd like to offer for your consideration. These deal with the Data Sheet and its formulas.
1) The way the sheet is laid out now, you have the first five columns (column E = Customer) are data entry and are together, then a couple of calculated columns, then another data entry, followed by a calculated column then another data entry one with three calculated columns at the end. I'd suggest that all Data Entry columns be placed together as far to the left as possible. This will greatly simplify the data entry process by removing a lot of scrolling the right to get to the next column. Not only will this reduce the time needed but will help eliminate something being missed. Since everything is calculated by formulas, it doesn't matter to Excel where the numbers come from.
2-A) While this sheet will certainly work as it is, you might consider moving all calculated columns to another sheet. This will leave only the Data Entry ones here, so if someday your boss comes to you and says, "Anita, you did such a wonderful job last August in giving me that Summary report, could you include '????' so that information could be added to the Summary report or another one?" My boss has done that to me, and remember, these two guys must be related so they probably also think alike.
All you have do now is say, "Sure boss, no problem," then add the required Data Entry column to the end of what you already have. Everything is still together. By being so agreeable, this alone should get you a raise in salary.
2-B) If everything is together and you are interested in some column in the middle of a whole screen full of numbers, it can be a bit overwhelming and hard to follow with your eye.
2-C) If the Data Entry sheet gets to large, someday you may want to print it but you'll have so many columns they won't fit on a sheet of paper.
3) This deals with the formulas. It's true that I'm fairly new to Excel so I could be mistaken, but I think I'm right. If not, please correct me. I know the sample Data Entry sheet is only a demonstration, however, the formulas as they are now written that fill out the calculated columns, will access ONLY the first five rows of data, e.g. rows A3 to A7. Before you could add a sixth row, you will have to edit the formulas, mainly by removing the '$' signs that are in front of the numbers. After making those adjustments, then you can copy them down several rows and they will adjust themselves according to the number of data rows.
A possible solution to having to continually copy formulas, would be instead of using formula's, to write some VBA code. This will automatically adjust itself to the number of rows, and columns of data, regardless of how many you have. This may take a little longer to set everything up, however, in the long-run, it may pay off. Sometimes a little planning can go a long ways.
Just a few more things for you to consider.
TotallyConfused
