If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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!! |
Thread Tools | |
Display Modes | |
|
|