comparing a phone number to an allowed list???

**juzsp** · 02-20-2007, 09:35 AM

Hi there,

Im loosing my mind trying to work out how to do the following, if anyone could help I would be grateful.

I have our company telephone accounts and im trying to highlight any numbers that are not on our approved dialling list. What I would like to happen (ideally) would be for a formula to look at the cell next to it and compare it with the approved numbers list, if it doesn’t appear on the list then it would do something to let me know, like put a big red X in the cell (if it could make me a cup of Tea instead that would be better).

Can this even be done? (not the tea thing)

**oldchippy** · 02-20-2007, 09:47 AM

Take a look at the attached, I'm working on the tea thing now!

**VBA Noob** · 02-20-2007, 09:48 AM

Excel 2007 makes tea and coffee.

This link might help with your other problem

http://www.cpearson.com/excel/duplic...tingDuplicates

VBA Noob

**juzsp** · 02-20-2007, 09:49 AM

Sorry, i guess it has been asked before.

thanks for th quick reply though

**oldchippy** · 02-20-2007, 09:50 AM

No problem come back for more - thanks for the feedback

**juzsp** · 02-20-2007, 11:13 AM

well im back for more (already!!!)

Thanks for the help with the last question, it worked a treat. Now that an X appears every time there is a number that’s not on my allow list, I need to put the duration of the call in another cell so I can add it all up.

something like (I’m not very good with formulas so I will just say it)

=IF(“business number”=X)then(copy/Paste “duration” to D1))

Yes I know you will all have a good laugh at my “tell it how it is” formula, as I said, im not very good with excel.

Can anyone convert my formula into something that excel will understand?

I promises, no more questions… today.

**oldchippy** · 02-20-2007, 11:18 AM

Try this in D1

=IF(C1="X",A1,"") where C1 is column with X and A1 is duration, copy that down. The SUM to add it all up needs to be formatted to [hh:]mm:ss

**juzsp** · 02-20-2007, 11:31 AM

Wow, thanks again for the quick reply, worked perfect again!

When I try and SUM the times I just get 00:00:00. I remembered to format the SUM cell [hh]:mm:ss (I even tried changing the other cells to that format but it’s not working.

If I do (this is my excel level) =D1+D2+D3+D4 then it works, SUM the same cells and the result is always 00:00:00

is there anything else that I may need to change?

I feel so guilty taking up all your time

**oldchippy** · 02-20-2007, 11:39 AM

Just try this for first, select one of the cells that is actually "time format" not "text format", then click on the Paintbrush icon, then select the cell with you total in.

The problem you have is that the cells causing the problem are formatted to text and therefore will not sum.

**juzsp** · 02-20-2007, 12:00 PM

thanks oldchippy but its still not working.

SUM works if I type in new times elsewhere in the sheet, do the relevant formatting then SUM them up but not on the imported data. Guess that’s my problem.

*edit*
Just double checked, If I just retype the same data over the old data then it works.

*edit2*
Guess what, all I have to do is F2 to edit the cell, not make any changes and press enter then it works! Now I suppose I have to go through the entire worksheet doing this?

comparing a phone number to an allowed list???

LinkBack

Thread Tools

Rate This Thread

Display

comparing a phone number to an allowed list???

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions