Forum Discussion
Excel formulas
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
20 Replies
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
- (My point on the matter of sorting was to challenge the fact that you have two sheets, one sorted one way, the other another. Why two sheets?)
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.
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
- I assume you're meaning "Row" as in "What row of seats does this student sit in?" Without seeing what your sheets actually look like, it's hard to write the formula. But it occurs to me that you don't really need two sheets in order to have one sorted alphabetically, another by classroom. Excel has a "Sort" function, under Data...Sort, and that allows you to sort by any of the columns in the list. So you could have an alphabetical sort and then switch to classroom sort of the same list AND that list could include the Row data that apparently is in the one but not in the other. For some reason I'm not seeing the ability to upload a sample sheet right now as I write this reply... but if you highlight a list that includes two or three columns and then go to the "Data" menu item at the top of your screen, click on it, and you'll see "Sort" as one of your options. Clicking on that will give you the choice of columns to sort by, whether increasing or decreasing. You can have secondary and tertiary sorts..... Write back if this doesn't make sense.
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
