Forum Discussion
Excel Formula Help....
Not quite. I would like to simply enter a zip code and have the name of the recruiter populate. That's it.
I figured since you had the names of the schools you wanted to be able to give anybody calling from outside of your own zone of responsibility not only the name of the recruiter but also the several schools at which he or she might be found.
I have attempted to sort & filter and did not like the result due to the fact that it would skew the info of the other columns.
I'm not sure what you mean by "it would skew the info of the other columns"
IF there is only a one-to-one relationship between any given zip code and any given recruiter, a simple VLOOKUP would work with that same table.
Excel works far better with a single table as the source for information.
By way of background in Excel basics, it's often important in situations like this to separate the input (or data source) end of things from the output. Part of what made your original layout difficult to work with was that you'd (to this Excel basics way of thinking) confused the two, by putting time and energy into arranging multiple lists of schools and their associated zip codes under the names of each of the several recruiters, thus, in effect, needing whatever formula or function you might come up with have to look in multiple places for a single (and simple) piece of information. You also put time and energy into making it look nice. I have no issue with making things look nice, but would recommend that you begin with first making any workbook function; add the sizzle after it functions.
So here's a variation on what I set up last night, this time using VLOOKUP to find the entered zip code and deliver nothing but the name of the corresponding recruiter. And I just want to note that (because there are quite a few zip codes that appear more than once in your total database, this will just get the first instance of a match. That will serve your stated need IF there aren't any cases of two (or more) recruiters overlapping in a given zip code.
mathetes Thanks for your quick response!
I have no clue which formulas to use for this. I don't even know if it's possible. I consider myself to be a bit of a novice when it comes to Excel, but I find myself totally lost here:
In the attached spreadsheet. I'd like to associate each cell (school) with a specific zip code. That way when it is typed into the "calculator" at the top, it will bring up the person (column) associated with it.
I hope this makes sense! Thank you in advance!
I have no clue which formulas to use for this. I don't even know if it's possible. I consider myself to be a bit of a novice when it comes to Excel, but I find myself totally lost here:
Nobody is asking you what formula is to be used. What we're asking for is a business / functional description of what you want to do. In English, not in Excelese.
In the attached spreadsheet. I'd like to associate each cell (school) with a specific zip code.
Zip code in which the school is? Zip code(s) of all the members of the student body? Faculty and staff? You need to be more complete here. And whatever the answer is, you'd need to come up with a table that contains that info.
That way when it is typed into the "calculator" at the top, it will bring up the person (column) associated with it.
"It"? What is "it"? Name of the school? Zip code?
And so forth. A complete description in English of the functionality you're seeking. Don't worry about what formula, what function. That's for those who know Excel. But you're the only one who can describe what you want to accomplish, and you need to do it fairly extensively and fairly precisely if you want anybody at this end to be able to come up with those formulas and functions, etc.
I have no doubt it can be done, by the way. We just need you to be more descriptive of what "it" is.
mathetes Thanks for your response. Sorry, for being vague. I'll try my best to better explain it...
Backstory: As a recruiter, I get phone calls from lots of applicants looking to join the military. Sometimes, I get calls from people who are outside of the zip code I am assigned. My coworkers have also noted these types of calls. I thought it would be a good idea to create a spreadsheet to distribute to my coworkers so that we could reference it and refer people to the appropriate recruiter.
On the attached spreadsheet, I have 12 columns: The headers alternate between the name of the recruiter and their assigned zip codes. Below that are the schools said recruiters are responsible for. For convenience, I included a "calculator" at the top that I would like to be able to perform a "quick search" in. The person using the spreadsheet should be able to search for a specific zip code and reveal the appropriate recruiter to the right of that.
Bottom Line Up Front: I am really only hoping for a way to apply the text in the red columns and the numbers in the blue columns to their respective cells at the top of the spreadsheet.
I really do hope this helps clarify things a bit better! Thank you for your help.
This is not the prettiest layout, but it is functional. You enter a Zip code and the spreadsheet will list all the schools and the recruiter(s) assigned.
Because there are multiple schools sharing some zip codes, I ended up using the FILTER function. That is the only function that was needed. You can move things around to make it look more attractive, but the one thing you should keep constant is the way the database on the left is organized: a single database. It could be a different sheet altogether, as far as that's concerned. If you're going to be adding more rows, you'll need to adjust the FILTER formula to reflect the added rows. Or we could make it a formal Excel Table, and I'd be happy to help you with that. But first let's just make sure that this is doing what you want it to do.
- Not quite. I would like to simply enter a zip code and have the name of the recruiter populate. That's it.
I have attempted to sort & filter and did not like the result due to the fact that it would skew the info of the other columns.