Way to verify information

Kiloelectronvolt

New Member
Joined
Oct 5, 2015
Messages
45
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 .
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
866
Office Version
365
Platform
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,743
Office Version
365
Platform
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

New Member
Joined
Oct 5, 2015
Messages
45
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

New Member
Joined
Oct 5, 2015
Messages
45
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
866
Office Version
365
Platform
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

New Member
Joined
Oct 5, 2015
Messages
45
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
866
Office Version
365
Platform
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

New Member
Joined
Oct 5, 2015
Messages
45
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,186
Messages
5,509,680
Members
408,748
Latest member
tevian

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top