[Solved] Match and verify cell content

[MrProgrammer]

I am trying to create a spreadsheet to test memory function. Sheet 2 has a list of phone numbers i.e. 403-278-4040 in A5 which I want to link to Sheet 1 in A9. I want to compare the content of cell A9 in Sheet 1 to the content of cell A5 in Sheet 2. I type in the number in A9 and if it matches the content in A5 the cell will change color to yellow. If it doesn't match the cell turns pink until I get it right.

[RusselB]

Welcome to the Forums.
I think you really want to have just one number from the list on Sheet 2 to be referenced in Sheet1.A5, rather than the full list.
If so, how do you want that to be selected/chosen?
Once we have that, then the rest can be done using either the STYLE function or Conditional Formatting.

[Thracian]

Hi, and thanks for the welcome. I have looked at the Referencing function. But as far as I can tell that just shows me in Sheet 1 what is in the Sheet 2 reference cell. What I want to do is enter the phone number in the empty cell in Sheet 1 and have it compare to the value in Sheet 2. Then let me know if it is a match or not.
There are 12 phone numbers in Sheet 2. Each number has a cell in Sheet 1. I work my way down thru the cells in Sheet 1 entering what I hope is the correct number.
Cheers

[RusselB]

Ok, I'm trying to understand what it is you are trying to do.
I think we have a misunderstanding here. I think I understand what it is you are wanting to do, what I don't know is how the number for Sheet2.A5 is picked from the list of numbers on Sheet 2.

[RusselB]

Rereading your last post, it appears that you want the numbers to be selected in order... not a problem...but what happens when you get to the end?

[Thracian]

It's strictly a comparison between the two cells. The value on Sheet 2 is fixed. As a memory training I enter what I believe to be the correct number Sheet 1 and the spreadsheet compares the two. If the value I enter on Sheet 1 matches the value on Sheet 2 then the cell on Sheet 1 will turn color (yellow for example). If the number I enter on Sheet 1 doesn't match then the color will be different (say pink) and will stay pink until I get the number right.
To explain the set up. Sheet 1 has a list of companies with a blank cell below. The cell below is where I enter the number. There are 12 companies listed. The corresponding phone number is on Sheet 2 . So I go to the Walmart cell (the cell just below the title cell) I enter what I believe to be the correct phone number. This cell is linked to the cell on Sheet that has the correct phone number. If the number I enter matches the value in Sheet 2 then it meets the criteria and the cell in Sheet 1 will change to yellow. If the value is incorrect then the color goes to pink to indicate that I need to try again.
I hope this helps.
Cheers

[RusselB]

It helps in that I have more information, unfortunately I still don't have the information I need, and it sounds like the layout of your spreadsheet is going to make this even harder.
Can you upload a sample of your spreadsheet so that I can actually study the layout? Some dummy data would also be helpful.
See How to attach a document here for information regarding uploading your spreadsheet.

[Thracian]

I'm back. I have included a copy of the file. As you can see it isn't a very complicated file. There are notes in the file to explain what I want to do.
Cheers

[Thracian]

Happy New Year! Almost.
I think I have a solution to this problem. Not two Sheets just one.
Column A is list of companies or individuals.
Column B is the test entry
Column C is the actual phone number (this column gets hidden)
Column D is where the formula resides and where the response shows up
The formula and example:
=IF(B2=C2;"Match!";"")
This seems to work quite well.
I have noticed that for me the pattern needed to dial the call helps me a lot. Anyone know of a way to create a "keypad" for each test entry which would allow me to "dial" the number for the test rather than just type it in?
I have 30 numbers so far and have remembered 15. Try to run thru this exercise two or three times a day.

[Villeroy]

No software can do this unless you write the blue names in the exact same way as in Sheet2, column B. The software can not look up "Co-op oakfield" if it is listed as "Coop" on the other sheet. A spreadsheets is easier to maintain without funny layout.

[Thracian]

Hello again.
The spreadsheet I included in a previous submission is useless. As I mentioned in my last entry I found that using a single sheet rather than two works quite well. The only columns I need to work with are
Column B (where I enter what I believe to the correct phone number).
Column C (the correct phone number and this column is hidden)
Column D (has the formula and where the response will display)
No problem!
Maybe I should label this problem as solved.
I did ask how to create a virtual keypad linked to the B column cells so I can "dial" the number just as would if I were to dial my phone. Maybe I should start a new query?
Thanks for the feedback!

[Villeroy]

There is a file attached to my posting. You did not download the file.

[Thracian]

Sorry! Didn't think to view it. Here is my latest spreadsheet

[Thracian]

Let me try this again. The column in yellow is to be hidden. You enter the phone number you believe to be correct in Column B and the "Match!" appears in Column D if you get it right. If you enter the incorrect number "Wrong!" appears.