Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Multiple Criteria to filter for multiple users
Earlier in the week someone was kind enough to provide me with the following
code that allowed me to search for different numbers in a column and provide me with a answer of True or False. While that was extreme helpful, I was wondering if it is possible to search and depending on the values, return a person's name as shown below. =OR(D2=27028,D2=27076,D2=27286) Tim's =OR(D2=27310,D2=27312) Bobby's =OR(D2=27670,D2=28088,D2=28094) Alex =OR(D2=28291,D2=28386,D2=28488,D2=28630) Sally's Thank you for your assistance. Your help is Greatly Appreciated!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Multiple Criteria to filter for multiple users
Put each OR function inside a IF function, like so:
=IF(OR(D2=27028,D2=27076,D2=27286),"Tim's","") =IF(OR(D2=27310,D2=27312),"Bobby's","") ....etc. Of note, guessing on how's your setting up your formulas, you might want to look at the LOOKUP and VLOOKUP functions. I'm thinking you could set up a table somewhere that lists the numbers you are checking, correlated with names. Might save you some time. But again, just speculating based on these few formulas. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "fgwiii" wrote: Earlier in the week someone was kind enough to provide me with the following code that allowed me to search for different numbers in a column and provide me with a answer of True or False. While that was extreme helpful, I was wondering if it is possible to search and depending on the values, return a person's name as shown below. =OR(D2=27028,D2=27076,D2=27286) Tim's =OR(D2=27310,D2=27312) Bobby's =OR(D2=27670,D2=28088,D2=28094) Alex =OR(D2=28291,D2=28386,D2=28488,D2=28630) Sally's Thank you for your assistance. Your help is Greatly Appreciated!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Multiple Criteria to filter for multiple users
In the last few minutes I have found that the original formula does not
always return a correct answer. Is there something else that will return more consistant results? As to the the LOOKUP and VLOOKUP functions, could the table reside on the hidden "personal.xls" workbook that I save my macros on? Thank you! "Luke M" wrote: Put each OR function inside a IF function, like so: =IF(OR(D2=27028,D2=27076,D2=27286),"Tim's","") =IF(OR(D2=27310,D2=27312),"Bobby's","") ...etc. Of note, guessing on how's your setting up your formulas, you might want to look at the LOOKUP and VLOOKUP functions. I'm thinking you could set up a table somewhere that lists the numbers you are checking, correlated with names. Might save you some time. But again, just speculating based on these few formulas. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "fgwiii" wrote: Earlier in the week someone was kind enough to provide me with the following code that allowed me to search for different numbers in a column and provide me with a answer of True or False. While that was extreme helpful, I was wondering if it is possible to search and depending on the values, return a person's name as shown below. =OR(D2=27028,D2=27076,D2=27286) Tim's =OR(D2=27310,D2=27312) Bobby's =OR(D2=27670,D2=28088,D2=28094) Alex =OR(D2=28291,D2=28386,D2=28488,D2=28630) Sally's Thank you for your assistance. Your help is Greatly Appreciated!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Multiple Criteria to filter for multiple users
We would have to know more about what errors you are seeing to diagnose why
you are not getting consistent results, I'm afraid. And yes, you could put the table on a hidden workbook, as long as you properly reference it in the formula (easiest way is when you setup formula, physically highlight the cells in your Personal.xls so XL creates a proper link. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "fgwiii" wrote: In the last few minutes I have found that the original formula does not always return a correct answer. Is there something else that will return more consistant results? As to the the LOOKUP and VLOOKUP functions, could the table reside on the hidden "personal.xls" workbook that I save my macros on? Thank you! "Luke M" wrote: Put each OR function inside a IF function, like so: =IF(OR(D2=27028,D2=27076,D2=27286),"Tim's","") =IF(OR(D2=27310,D2=27312),"Bobby's","") ...etc. Of note, guessing on how's your setting up your formulas, you might want to look at the LOOKUP and VLOOKUP functions. I'm thinking you could set up a table somewhere that lists the numbers you are checking, correlated with names. Might save you some time. But again, just speculating based on these few formulas. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "fgwiii" wrote: Earlier in the week someone was kind enough to provide me with the following code that allowed me to search for different numbers in a column and provide me with a answer of True or False. While that was extreme helpful, I was wondering if it is possible to search and depending on the values, return a person's name as shown below. =OR(D2=27028,D2=27076,D2=27286) Tim's =OR(D2=27310,D2=27312) Bobby's =OR(D2=27670,D2=28088,D2=28094) Alex =OR(D2=28291,D2=28386,D2=28488,D2=28630) Sally's Thank you for your assistance. Your help is Greatly Appreciated!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Multiple Criteria to filter for multiple users
There are no errors. Formula returns False event though the value in the cell
equals one of the values in formula. I have tried to reformat the cell to text or number to see if that is the issue, but the answer is still False. Thank you! "Luke M" wrote: We would have to know more about what errors you are seeing to diagnose why you are not getting consistent results, I'm afraid. And yes, you could put the table on a hidden workbook, as long as you properly reference it in the formula (easiest way is when you setup formula, physically highlight the cells in your Personal.xls so XL creates a proper link. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "fgwiii" wrote: In the last few minutes I have found that the original formula does not always return a correct answer. Is there something else that will return more consistant results? As to the the LOOKUP and VLOOKUP functions, could the table reside on the hidden "personal.xls" workbook that I save my macros on? Thank you! "Luke M" wrote: Put each OR function inside a IF function, like so: =IF(OR(D2=27028,D2=27076,D2=27286),"Tim's","") =IF(OR(D2=27310,D2=27312),"Bobby's","") ...etc. Of note, guessing on how's your setting up your formulas, you might want to look at the LOOKUP and VLOOKUP functions. I'm thinking you could set up a table somewhere that lists the numbers you are checking, correlated with names. Might save you some time. But again, just speculating based on these few formulas. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "fgwiii" wrote: Earlier in the week someone was kind enough to provide me with the following code that allowed me to search for different numbers in a column and provide me with a answer of True or False. While that was extreme helpful, I was wondering if it is possible to search and depending on the values, return a person's name as shown below. =OR(D2=27028,D2=27076,D2=27286) Tim's =OR(D2=27310,D2=27312) Bobby's =OR(D2=27670,D2=28088,D2=28094) Alex =OR(D2=28291,D2=28386,D2=28488,D2=28630) Sally's Thank you for your assistance. Your help is Greatly Appreciated!! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Multiple Criteria to filter for multiple users
If the value is already entered, I'm afraid you can't simply change the
format to get it to switch. (I think you're on right track, numbers are stores as text somehow). Go ahead and set the format of the cells to number. In a spare cell somewhere, input 1. Copy this cell. Now, select your other cells with numbers in them, right-click, paste special, and select multiply. This will convert any numbers stored as text into actual numbers. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "fgwiii" wrote: There are no errors. Formula returns False event though the value in the cell equals one of the values in formula. I have tried to reformat the cell to text or number to see if that is the issue, but the answer is still False. Thank you! "Luke M" wrote: We would have to know more about what errors you are seeing to diagnose why you are not getting consistent results, I'm afraid. And yes, you could put the table on a hidden workbook, as long as you properly reference it in the formula (easiest way is when you setup formula, physically highlight the cells in your Personal.xls so XL creates a proper link. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "fgwiii" wrote: In the last few minutes I have found that the original formula does not always return a correct answer. Is there something else that will return more consistant results? As to the the LOOKUP and VLOOKUP functions, could the table reside on the hidden "personal.xls" workbook that I save my macros on? Thank you! "Luke M" wrote: Put each OR function inside a IF function, like so: =IF(OR(D2=27028,D2=27076,D2=27286),"Tim's","") =IF(OR(D2=27310,D2=27312),"Bobby's","") ...etc. Of note, guessing on how's your setting up your formulas, you might want to look at the LOOKUP and VLOOKUP functions. I'm thinking you could set up a table somewhere that lists the numbers you are checking, correlated with names. Might save you some time. But again, just speculating based on these few formulas. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "fgwiii" wrote: Earlier in the week someone was kind enough to provide me with the following code that allowed me to search for different numbers in a column and provide me with a answer of True or False. While that was extreme helpful, I was wondering if it is possible to search and depending on the values, return a person's name as shown below. =OR(D2=27028,D2=27076,D2=27286) Tim's =OR(D2=27310,D2=27312) Bobby's =OR(D2=27670,D2=28088,D2=28094) Alex =OR(D2=28291,D2=28386,D2=28488,D2=28630) Sally's Thank you for your assistance. Your help is Greatly Appreciated!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter duplicates by multiple criteria | New Users to Excel | |||
Filter Multiple Columns on Multiple Criteria and count the last co | Excel Worksheet Functions | |||
Best Way for Multiple Criteria to Show From a Filter | Excel Discussion (Misc queries) | |||
Filter a range of data with multiple criteria | Excel Discussion (Misc queries) | |||
Create macro to filter on multiple criteria | Excel Worksheet Functions |