Way to verify information

Kiloelectronvolt

Board Regular
Joined
Oct 5, 2015
Messages
57
Office Version
  1. 365
Platform
  1. Windows
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:

Business name in A1 and address in B1.

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
Joined
Nov 18, 2019
Messages
927
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 1, 2002
Messages
1,786
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 5, 2015
Messages
57
Office Version
  1. 365
Platform
  1. Windows
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.
 

Kiloelectronvolt

Board Regular
Joined
Oct 5, 2015
Messages
57
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 18, 2019
Messages
927
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Oct 5, 2015
Messages
57
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 18, 2019
Messages
927
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 5, 2015
Messages
57
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top