Forum Discussion
Excel formulas
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 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!
- If you're only getting a "few N/As" that suggests possibly the SchoolIDs aren't being found for those few students. Off hand I can't think of another cause for that. One of the essential parts of a viable database is a reliable and consistent "key"--otherwise known as a unique identifier. Sometimes, even as simple a matter as an extra (but invisible) space in the field of one or the other of the two spreadsheets entries for that field..... so check the SchoolID field for those few N/A cases...to make sure they're identical even to the point of having no "trailing space" after the last digit. The VLOOKUP is looking for an EXACT match.
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
- You're very welcome, Mary. I love using Excel (much prefer designing a spreadsheet to playing computer/video games)...and am learning since joining this site last week, just a day or two before you did, that Excel has added features in the last decade or more that I didn't know about (I retired in 2002 and have continued using Excel personally, but not keeping up on all the changes)...
Anyway, feel free to come back with questions about the solution I've given you. Depending on the volume of lunch purchases, the number of students, etc., there are surely more elegant ways to meet your needs. I have some ideas, but they'd involve major changes that may not be worth it in your situation.
John (mathetes) 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
- Woops...that $I$3 was a mistake on MY part. I'm sorry. It should have read $E$3....
but please use the named range tool.
If that was your only issue, that's great. You'll see that the "range" identified in the New & Improved is different, much smaller, because of the different layout.
If that works for you, I"ll make one more change which will allow the named range to grow as you add names. Let me know if you'd like that.