75ZT Community

Trying to sort this out at work.

We have a liability for works carried out by certain companies. These are all identified by a six figure number.
We have a list of numbers we are liable for.
We also have a list of their contact numbers, names and addresses.

I need to be able to manually enter the 6 figure number into a cell, this will then recognise the number (or not) and flag up "liability" or "no liability" (or similar) in a separate cell. A third cell then needs to display company details contact numbers etc if indeed we are liable.

Anyone fancy the challenge of explaining to little numpty me how to do this

Thanks in advance

Posted 01 Oct 2015, 08:18 #1 

Paul

That apart Mrs Lincoln, did you enjoy the play

Posted 01 Oct 2015, 11:01 #2 

Assume your 6 figure numbers are in Column A, range A1 to A10 (A1:A10)

Then assume you are going to enter the numbers you are checking in Column B, starting at B1.

Put this formula into Column C, dragging down as necessary

Code: Select all

=IF(ISNUMBER(MATCH(B1,A1:A10,0)),"Liability","No Liability")

(adjust references to cells/ranges as necessary to work with what you already have in your sheet

Posted 01 Oct 2015, 13:52 #3 

Last edited by Dave on 01 Oct 2015, 21:29, edited 1 time in total.

Oh, and you could improve this...

As a blank cell will give the result "No Liability", to make the returns clearer use either

Code: Select all

=(IF(B1="","",IF(ISNUMBER(MATCH(B1,A1:A10,0)),"Liability","No Liability")))

or

Code: Select all

=(IF(ISBLANK(B1),"",IF(ISNUMBER(MATCH(B1,A1:A10,0)),"Liability","No Liability")))

Ideally, put a validation on the range of cells you are checking against and those where users enter the data, to ensure that the format matches (eg, if you have 12-34-56 as opposed to simply 123456) which will help to minimise errors!

Posted 01 Oct 2015, 13:57 #4 

Posted 02 Oct 2015, 08:57 #5 

Hi Dave, sorry, busy busy at work last couple of days.
Just started on this tonight, the middle formula is superb, took me ages to stop the text from showing green . The value entered in B1 will read from the values A1 to A12 ( or more if I alter the formula and report in C1 accordingly;)
So moving on is it possible that if the number is showing liability then another cell (C4?) can display contact details bespoke to that number etc?

Thanks again Dave, your a star.

Posted 02 Oct 2015, 17:15 #6 

Yes, if said details are stored on the Workheet (or indeed, elsewhere in the same Workbook) so that they can be retrieved, in the same or a similar fashion!

If you PM me a copy of the document (or a sample if the details are sensitive, ie addresses etc), I can have a better look!

Posted 02 Oct 2015, 18:08 #7 

Posted 03 Oct 2015, 07:38 #8 

You do not have the required permissions to view the files attached to this post.

Posted 03 Oct 2015, 12:30 #9 

Thanks for the thanks Gary!

LOL at the mug Mick! Not quite at Ninja level yet, but do enjoy a good spreadsheety based challenge!

Posted 03 Oct 2015, 13:01 #10 

Dave wrote:Yes, if said details are stored on the Workheet (or indeed, elsewhere in the same Workbook) so that they can be retrieved, in the same or a similar fashion!

If you PM me a copy of the document (or a sample if the details are sensitive, ie addresses etc), I can have a better look!

Will do Dave, Had a few things to sort out, Whilst I was working upstairs in the spare bedroom Friday (now my office), some scrote opened the house door walked in to the living room and helped themselves to wifes laptop. I know I should have locked the door but I forgot. wife is none too happy

Posted 03 Oct 2015, 18:37 #11 

Last edited by takestock on 03 Oct 2015, 19:14, edited 2 times in total.

That's not good at all Dave..... We are very careful about locking the front door, and also keep our side door locked, so anything like this would necessitate said scrotes going through two gates to appear at our French window. At the sound of the first gate, our dogs would be going mental! Not wanting to tempt providence, but hopefully our "alarm" system is quite effective!

Posted 03 Oct 2015, 18:46 #12