Oct 21 2019 04:49 AM - edited Oct 21 2019 05:00 AM
I am trying to pull a row number that is associated with a student record into a formula in a second sheet. The names are alphabetical in one and by classroom in the other
Oct 21 2019 05:20 AM
Oct 21 2019 05:29 AM
@madgateI wasn't able to download your sample for some reason...got a bunch of error and warning messages. but here finally is a quick example of a sortable list that should illustrate another possible solution. You can sort this by name OR by classroom....
There's a brief explanatory comment on the sheet
Oct 21 2019 07:24 AM
@mathetes thank you. No i have them sorted ok im just trying to pull the row number of the student from the lunch sheet to the formula in the alphabetized balance sheet. I can do it manually by finding the student in one and putting it in the formula it just takes time to do 300 of them
i attached a copy of my spreadsheet
Oct 21 2019 07:28 AM
Last Name | First Name | Teacher | Room | School ID | Family Total | Total Lunches September | Total Lunch Spent September | Total Milk | Total Milk Spent September | Total Family Spent September | Total Deposits September | Date Deposits September | End of Month Balance |
Adgate | Mary | mccool | k0-2 | $10.00 | 0 | $0.00 | 0 | $0.00 | $0.00 | $10.00 | |||
Adams | Amos | O'Reilly | K0-1 | $0.00 | 0 | $0.00 | 0 | $0.00 | $0.00 | $40.00 | Monday, June 3, 2019 | $40.00 | |
Anderson | Drew | Griswald | 6-16 | $23.00 | 1 | $3.00 | 1 | $0.50 | $3.50 | $19.50 | |||
Andrews | Owen | Barrows | K1-5 | $33.50 | 0 | $0.00 | 0 | $0.00 | $0.00 | $33.50 |
Oct 21 2019 07:31 AM
@mathetes this is the formula in the balance sheet =InputSept19!I388 and it pulls from the input sheet =InputSept19!I1388 so i was just looking for an easy way to populate the row (388) in this case instead of me having to manually enter it into the formula
thanks
Mary
Oct 21 2019 08:24 AM
Oct 21 2019 10:26 AM
@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
Oct 21 2019 02:39 PM
@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?
Oct 21 2019 07:00 PM
@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!
Oct 22 2019 06:51 AM
@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.
Oct 22 2019 08:20 AM
@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
Oct 22 2019 09:13 AM - edited Oct 22 2019 09:16 AM
@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.
Oct 22 2019 10:47 AM
@mathetes I didnt see the new and improved and it looks awesome! I know if I had time I couldve made it look pretty but I so appreciate your improvements. When I read your notes about the named range i wasnt sure if the rows you put in the notes were correct . I was a little confused.
"LunchPurchases" is a name I assigned to the range in the InputSept19 sheet beginning with column I, which contains the SchoolID for the student, and goes through Column AQ. I,e., it's almost that entire spreadsheet where you have the information. You could, in place of "LunchPurchases" put in $I$3:$AQ$17 but using a named range makes that easier to read the formula, to make sense of what's happening.
Column E is the school id in the inputSept19 sheet. I understand the formula but my range gave me an error. Thanks so much!
Oct 22 2019 10:58 AM
Oct 22 2019 11:11 AM
@madgate Here, Mary, is an even Newer & More Improved... the only difference in this newest one is that I've added a feature to enable the named range "LunchPurchases" to grow as student names and IDs get added. I did this only up to a max of 100. If in fact you have more, all you'd need to do is change the =COUNTA formula in cell B2 to a larger number......
So try this one out next month and let's see how it works. Other than the real names of students, it's all set for November.
John
Oct 22 2019 05:28 PM
@mathetes Awesome! I got the range to work in my original one Yours looks great and can't wait to use it I can't thank you enough for all your help. Your a godsend :)
Mary
Oct 22 2019 05:54 PM
Oct 22 2019 07:24 PM
@mathetes my school has a new billing program which isn't the end all be all so i am trying to get my spreadsheet working which has always worked great for me. Lot of catch up but it will be worth it. I also actually spoke to soon. When i cut and pasted the VLOOKUP formula I am seeing a few N/A's Can't figure out where the error is
Oct 22 2019 07:45 PM