Applying number formatting to a cell, dependent upon contents of another cell.

**IAPharmGirl** · 11-11-2016, 01:00 PM

I have a list of states in one column, and three types of licenses in the next three columns. Each type of license has a certain number format underneath it in the column.

I need to either:
1. Only allow entry of a number in a certain format, dependent upon the contents of 2 other cells.
ie: if your state is Iowa, and your license is for a Pharmacist, your number would be only a 5 digit number.

2. Highlight numbers in a column that do not match a certain format.
ie: once everyone enters their license numbers, I can sort them by state, but after that, can I highlight cells that are NOT 5 digit numbers?

**avk** · 11-11-2016, 03:17 PM

Dear IAPharmGirl : Plz refer attach file. Hope this you required.
-------------------------------------------------------------------------------------------------------------------
If you like the answer please say thanks by clicking the star in the bottom left hand corner of my post.
And the forumn rules require you to edit your original post to mark as [SOLVED].
Edit your original post in advanced edit and you should be good to go.

**dflak** · 11-11-2016, 03:29 PM

In your data entry, you will enter a state and a type of license and a license number. You want to look up based on the combination of state and type of license whether a number is the correct format. Is this correct?

**dflak** · 11-11-2016, 04:08 PM

Try the attached file.

I am not sure I could prevent a bad data entry, but the following can be used to flag bad data as it is entered. You’ll need a couple of helper columns (these could be hidden).

The key to this task is pattern matching. The attached picture shows some of the matches.

For example to match a 5 digit number, use #####. To match the Pharmacist for Alaska, us PHAP####. To match anything use *.

Some of the licenses could be a 4, 5, or 6 digit number. In this case, separate the patterns with commas like this: ####,#####,######.

I did patterns for the first couple of states. I will leave it for you to do the rest. I think if there is no license like Maine, you may have to use *.

There is one fancy formula in column D that does the lookup for the pattern: =VLOOKUP(A2,Table1,LOOKUP(B2,{"Intern","Pharmacist","Technician"},{6,5,7}),FALSE).

The LOOKUP(B2,{"Intern","Pharmacist","Technician"},{6,5,7}) part finds the column position for the VLOOKUP based on the type of license seletected.

Good luck with your project. If you get stuck on a pattern let me know.