SOLVED

Excel Formula Help....

Copper Contributor

Hello,
I am looking for help in developing my Microsoft Excel spreadsheet. I am attempting to create a sort of "calculator". I believe I need to use "If cell contains certain text, put a value in another cell".  I would like to get in contact with someone who could potentially help me with this?

11 Replies

@shardae 

 

I'm sure there are plenty of people here who'd be happy to help. You'll need to be a lot clearer, though, on what it is you're trying to accomplish. The spreadsheet doesn't really give a hint at what the "IF" condition might be, what you'd be testing for, what you'd want done with any given result.

@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!

@shardae 

 

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.

@shardae 

 

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.
best response confirmed by shardae (Copper Contributor)
Solution

@shardae 

 

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.

 

Wow! This is much better than anything I would have been able to come up with! I also appreciate that you were also able to list the recruiters and schools as the column instead of the row. You were right, it functions much better this way!

Thank you so much!

@shardae 

 

Wow! This is much better than anything I would have been able to come up with!

 

I've been at this kind of thing for at least 40 years now (since the mid-70s), so I should have learned a few things in that time. But thank you. The VLOOKUP function is, from what I've read, the most common function after the basics of SUM and the like. And it is a very useful function to become familiar with. As I just said, there are other ways to do the same thing--within Excel, users often comment on how many different ways there are to "skin the same cat."

 

In this very slightly revised version of that, I've put the Lookup (the user-oriented page) on a sheet of its own, in effect hiding the database on a second sheet. You could even hide that altogether, although that isn't really necessary in your small fraternity of users. Just showing you what can be done.

 

You are aware, I trust, that there are several rows in the database where there's no zip code--I don't know if you just hadn't found it yet, or you were thinking it was the same as the row above. This functions with those blanks, but if in fact the missing zip codes are unique, this whole little application you've created would be incrementally more complete if you could add those in.

 

I also was thinking that (unless there's a privacy issue) you could enhance this further by adding the phone numbers of each recruiter, and/or an email address. That would involve adding column(s) to the database. Or, if we wanted to get fancy, another side database connecting each name with that contact info; this would be more elegant. Let me know if that would interest you.

 

I also appreciate that you were also able to list the recruiters and schools as the column instead of the row. You were right, it functions much better this way!

 

VLOOKUP, FILTER, INDEX and MATCH....that whole set of data retrieval functions...all work with a single database. There is also Power Query now, with some of the newest editions of Excel, and with that you can combine multiple data sources. Useful in advanced applications, but far more power than you need here.

 

Thank you so much!

 

You're very welcome. I hope your recruiting efforts are helped by all of this.

@shardae 

 

Here's a quick enhancement: I created a separate table with the name of the recruiter, telephones and email addresses.  You'd have to put the real ones in, but this shows how easily a simple table can be added, and a second set of lookups that use the result of the first (name of recruiter, in this case) to retrieve that person's telephone or email.

 

By the way, both VLOOKUP functions now are looking into a named range to retrieve the requested information. If you add rows, which is entirely possible, you may need to extend those ranges.

@mathetes My apologies for the late response!

Yes, I left a few of the zip codes blank due to the fact that there were multiple schools with the same name in the area; so after I got my coworkers to verify the correct ones, I added them in. I noticed the "Zip Code not found" error message. I like that!

 

I really do appreciate all your help!

1 best response

Accepted Solutions
best response confirmed by shardae (Copper Contributor)
Solution

@shardae 

 

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.

 

View solution in original post