We offer Para transits rides to people. but I have a list of clients that I cant offer rides to anymore. the list is about 500 names long. I need to have an alert or highlight a name if it pops up. Any one have any ideas on how to make this happen in excel?
use conditional formatting and a lookup to the list of names
Only issue will be if they do not enter the name the same way as you have
ie
J Smith
John Smith
J. Smith
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Thanks for the reply Wayne. How would I do that if my list was in another sheet and I import my new list from another program? So for instance every day I will import 700 clients into excel, and I have a list of like 500 clients that Ilike excel to check and make sure they are not on the newly imported list. I would like to have it color the cell if it finds a name. I do apologize now im not an excel expert, far from it.
you could use a vlookup() or indexmatch()
if you can load a sample - then we can see your exact data layout and advise more accurately
Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.
To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
All I have is a list of names in a basic excel file with the acct #'s of the client that I don't want to transport
EXAMPLE:
CLIENT NAME # OF CANCELLATIONS
SAM, NATHANIEL: LOG31843 49
NIVISON, NICOLE: LOG26576 43
BEAN, SHEILA: LOG26301 43
KATE, LINDA: LOG26321 41
JOHN DOE: LOG31627 38
JANE DOE: LOG26290
KIM HATT: LOG26685 36
test, test: 29417 35
BOBBY, JO: LOG26542 34
Do you want me to send it in excel?
attached is what I would like to happen the tab labeled new import would be the tab I would import the new client list. the tab labeled no fly would be the list that has the clients that I don't want to offer rides to. I would like excel to color the cell red if a name came up that was on the no fly list. Not sure if I explained it well enough or not.
could not attach before
see here
you can use
=NOT(ISERROR(INDEX('NO FLY LIST'!$A:$A,MATCH('NEW IMPORT LIST'!A1,'NO FLY LIST'!$A:$A,0))))
in a conditional format formula - see attached
for 2007, 2010 or 2013 excel version
Conditional Formatting
Highlight applicable range >>
$A:$A
Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=NOT(ISERROR(INDEX('NO FLY LIST'!$A:$A,MATCH('NEW IMPORT LIST'!A1,'NO FLY LIST'!$A:$A,0))))
Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks