# Way to verify information

#### Kiloelectronvolt

##### Board Regular
Hi All,

I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference:

Example: Ebay 1123 Fake Street

On my second sheet, I want to type in Ebay, and if I type in the wrong address, it will highlight the address red .

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### CA_Punit

##### Well-known Member
So you can try this

I have data Ebay in Sheet 2 and 1123 Fake Street in Sheet 2
and i have done the conditional formatting in Sheet 3 where i have Ebay in A1

so consider this

Book1
ABC
1Ebay
2
3
4
5
Sheet3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B1Expression=ISBLANK(\$B\$1)textNO
B1Expression=VLOOKUP(A1,Sheet2!A:B,2,0)<>\$B\$1textNO

#### sykes

##### Well-known Member
I think there's a better way (there's always a better way!)...

Instead of trying to trap a typo error, it would be quicker, easier, and more accurate, to use a formula in your second sheet, to automatically pick off the address from the first sheet, for you. In other words, type the retailer name into the first column on the second sheet, and have the correct address automatically populate.

Assuming your original data's in A1:B20 (I've used Sheet9 for my example - change to suit), and you want to type the retailer into column A on your second sheet, and have the associated address populate in column B, copy & paste the following into B1 of your second sheet, then fill down with the fill handle.

=IFNA(INDEX(Sheet9!B\$1:B\$21,MATCH(A1,Sheet9!A\$1:A\$21,0)),"")

Change the range references & sheet name, to suit, of course.

Cell Formulas
RangeFormula
B1:B8B1=IFNA(INDEX(Sheet9!B\$1:B\$21,MATCH(A1,Sheet9!A\$1:A\$21,0)),"")

HTH

#### Kiloelectronvolt

##### Board Regular
So you can try this

I have data Ebay in Sheet 2 and 1123 Fake Street in Sheet 2
and i have done the conditional formatting in Sheet 3 where i have Ebay in A1

so consider this

Book1
ABC
1Ebay
2
3
4
5
Sheet3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B1Expression=ISBLANK(\$B\$1)textNO
B1Expression=VLOOKUP(A1,Sheet2!A:B,2,0)<>\$B\$1textNO

This is almost working perfect for me!!! I have one more battle. I am only trying to use the first 3 numbers in the "address" field.

=VLOOKUP(B241,Sheet1!A:B,2,0)<>\$E\$241

So, using this formula works great, but can I have \$E\$241 only pull the first 3 characters? I found this formula online, which works on it's own cell, but IDK how to incorporate into the formula below. =CELL("contents",\$C\$1)

Thank you.

SYKES: I appreciate your response greatly, in this case I want my employees to manually type in the numbers, and have the system tell them if they entered it wrong. This spreadsheet is the fail safe, and I don't want to risk them overlooking it with it pulling the data automatically.

#### CA_Punit

##### Well-known Member

but can I have \$E\$241 only pull the first 3 characters?

#### Kiloelectronvolt

##### Board Regular
So, I was trying to keep my question short, but this is the way this works.

241 is a 5 digit number in the cell. But, I really only want it to check the first 3 numbers against the reference sheet.. (on the reference sheet it's only the first 3 numbers)

#### CA_Punit

##### Well-known Member

so suppose in cell 241 the returned value through lookup is 11115 street . Now you want to look whether the value entered in 241 matches only the first 3 character.

So if i enter 111 in Cell 241 it should not show red colour????

#### Kiloelectronvolt

##### Board Regular
so suppose in cell 241 the returned value through lookup is 11115 street . Now you want to look whether the value entered in 241 matches only the first 3 character.

So if i enter 111 in Cell 241 it should not show red colour????

Close, but you have it flipped. 111 will be the lookup and 11115 will be the cell 241.

#### CA_Punit

##### Well-known Member
i am more confused so please bear with my confusion. We will together solve it

What i understood is you want to check \$E\$241 which is the address cell with the Look-up value (which can be 24111). Only three character to be matched.??

#### Kiloelectronvolt

##### Board Regular
i am more confused so please bear with my confusion. We will together solve it

What i understood is you want to check \$E\$241 which is the address cell with the Look-up value (which can be 24111). Only three character to be matched.??

I'm sorry for my lack of details in the beginning of all this. it's my fault. Your formula works great for what I asked, but there is a part 2 I'd like to accomplish if possible.

Every address has it's own 3 digit property code that we use. So If I was to work at your house, I would assign you a 3 digit property code. Your property code is now 111. The first project we do, will be 01. So the full job ID is now 11101. And if we go back and work on another job, it will be 11102, and so on.

When we get a contract, my staff enters your name CA_Punit in B241 and your full 5 digit ID in E241. So E241 will say 11101. The REFERENCE sheet we are looking up, only uses the prefix.

So it's lookup sheet is like this:
CA_Punit 111
NEIGHBOR 112
NEIGHBOR 113

So if you hire me for a 3rd job, My staff will enter a row CA_Punit and then the next ID will be 11103. What I want to happen, is that excel will lookup to see if 111 (the first 3 digits) is your property code is correct. If it's wrong, I want it to turn red.

Replies
14
Views
110
Replies
1
Views
25
Replies
3
Views
44
Replies
6
Views
125
Replies
3
Views
126

1,132,883
Messages
5,655,775
Members
418,236
Latest member
jess5789

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back