Forum Discussion
Excel formulas
I was finally able to download your sample sheets this time. I'll have to say (in all honesty) that I'd most likely take a totally different approach (I think) to designing your "InputSept19" sheet. It looks like you're trying to do several things at once, possibly....track what each student has ordered (or how much to charge on a school bill) or some such.....AND track how many different entrees get ordered on different days of the month, for Kitchen Inventory purposes. So let me leave it at that for now and try to address your immediate question.
The VLOOKUP function might accomplish what you're trying to do. You'd use the SchoolID field as the key identifier in the formula--it's unique to each student, where the name is not necessarily unique--and then retrieve the data in the row and column you want by means of that.
HOWEVER the way your INPUT sheet is designed is not ideal for that purpose, (or at least it's not clear to me what the underlying pattern is, and how it might be made consistent from month to month. Is it only column I (as in I388) that you are looking for. In your live spreadsheet, it seemed to me you were pulling from column AQ, which carries the heading "Total Milk" ... so I'm asking is that always what you want? AQ4, then AQ5, then AQ6,....? If so, VLOOKUP could work.
But before we go forward (unless some other reader here has another idea) could you spend some time talking about what your underlying purpose is with these two spreadsheets? It looks like you're tracking something to do with lunch orders per student, but at what level of detail, etc.
The more complete your description of the "business need" here, the more we can help in the design, which might mean more than simply giving you a formula. You want to do more than put a bandage on the problem; you want to solve the underlying need.
mathetes thank you so much! I'll take all the help I can get. One of my workers created this spreadsheet years ago and it has worked for me for the past few years. Yes in the input sheet I enter in the lunches and the drinks that students consume on a daily basis. The total milk and the total lunch number is then carried over to the SeptBalance sheet which deducts the amounts from each families account and then I get alerted when their account is low and I send them an email. Does that help I hope.
Mary
madgateOK, Mary. I hope this helps. I've incorporated the VLOOKUP function to accomplish your goal. There are also some fairly extensive comments in the form of text boxes inserted into your spreadsheet to explain what I've done and how it works. I'm sure you'll still have questions, so feel free to come back. But try to play around a bit with both that VLOOKUP function AND the "named range" concept. I tried to explain both.
A question for you, though: this workbook--the INPUT sheet in particular--bears all the marks of a process that was done on paper for some time before anybody thought of using Excel...and all that was done was to copy the paper format (I envision a big sheet of green accounting paper), using Excel solely to do the basic math, adding and subtracting, maybe a little multiplication along the way. Unless you really need to track all of these by day and by type of lunch (!!) it would seem to me that that initial collection of "input" data could be radically simplified. Before I make specific recommendations, though, I'd want to know whether you DO need to know what was served for lunch each day, and whether (for that matter) you need to know which specific days each student HAD lunch and HAD milk.
How does that "1" get entered in the InputSept sheet in the first place? Is somebody sitting in the lunch room as the students come through the line, entering a 1 in the column for the day? Same for milk?
mathetes ive been looking at what you sent me the only place i'm having issues is with the named range i think im doing something wrong
thanks
Mary
madgate First, have you opened the newest one that I sent? (You might still be having issues; I just want to make sure you've seen that "New & Improved" version.
Second, can you describe the issues a bit more precisely. Have you been trying to create a named range and just not finding the menu spot....or are you just not making sense of what it is and why use it?
If/when you start using this system in real life, you'll be adding more names, I'm sure...more School IDs, etc.,, and when that happens the range will need to be enlarged to cover the entire student body. I only recently myself learned a way to do that "dynamically" so you don't have to do it by re-entering cell addresses.
But let's first just see if we can get you to the point of understanding VLOOKUP using a named range as the table of data from which you extract the numbers you want.
mathetes thank you again. I'll look at it tomorrow And i input the 1 into the spreadsheet from the lunch/milk cards that the students put in a bowl. I do need the totals by day and at the end of the month I need to report my numbers.
I'll get back to you tomorrow!
madgate Here's a "New & Improved" version. I've changed the Input sheet in particular to make it both prettier and, I trust, easier to use. All you need to do on the dates across the top, for instance, is enter the first school day of the month, and all the rest are calculated, skipping weekends automatically.
I've also used the "freeze panes" feature, so that you're not lost off at the right, not being able to see the names of the students. Now the input column for lunch or milk is visible on the screen close to the list of names. It could be made even closer....
Take a look and come back with any of your questions.