[Solved] Match and verify cell content

Discuss the spreadsheet application

[Solved] Match and verify cell content

Postby Thracian » Mon Nov 25, 2019 8:53 pm

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.
Last edited by MrProgrammer on Wed Jan 01, 2020 7:04 am, edited 2 times in total.
Reason: Added ✓. Changed title, was: Match and verify cell content - Solved!
OpenOffice 4.1.5 on Windows 10
Thracian
 
Posts: 8
Joined: Mon Nov 25, 2019 8:29 pm

Re: match and verify cell content

Postby RusselB » Tue Nov 26, 2019 12:55 am

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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6157
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

match and verify cell content

Postby Thracian » Tue Nov 26, 2019 1:34 am

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
OpenOffice 4.1.5 on Windows 10
Thracian
 
Posts: 8
Joined: Mon Nov 25, 2019 8:29 pm

Re: match and verify cell content

Postby RusselB » Tue Nov 26, 2019 4:23 am

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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6157
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: match and verify cell content

Postby RusselB » Tue Nov 26, 2019 4:29 am

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?
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6157
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: match and verify cell content

Postby Thracian » Tue Nov 26, 2019 6:56 am

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
OpenOffice 4.1.5 on Windows 10
Thracian
 
Posts: 8
Joined: Mon Nov 25, 2019 8:29 pm

Re: match and verify cell content

Postby RusselB » Tue Nov 26, 2019 7:35 am

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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6157
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Match and verify cell content

Postby Thracian » Tue Nov 26, 2019 9:17 pm

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
Attachments
memory-test.ods
This file has notes which I hope will help. Good luck Mr Phelps,,,, cue the music
(15.43 KiB) Downloaded 38 times
OpenOffice 4.1.5 on Windows 10
Thracian
 
Posts: 8
Joined: Mon Nov 25, 2019 8:29 pm

Re: Match and verify cell content

Postby Thracian » Tue Dec 31, 2019 7:37 pm

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.
OpenOffice 4.1.5 on Windows 10
Thracian
 
Posts: 8
Joined: Mon Nov 25, 2019 8:29 pm

Re: Match and verify cell content

Postby Villeroy » Tue Dec 31, 2019 8:13 pm

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.
Attachments
memory-test.ods
(14 KiB) Downloaded 16 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Match and verify cell content

Postby Thracian » Tue Dec 31, 2019 8:36 pm

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!
OpenOffice 4.1.5 on Windows 10
Thracian
 
Posts: 8
Joined: Mon Nov 25, 2019 8:29 pm

Re: Match and verify cell content - Solved!

Postby Villeroy » Tue Dec 31, 2019 8:54 pm

There is a file attached to my posting. You did not download the file.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Match and verify cell content - Solved!

Postby Thracian » Tue Dec 31, 2019 9:22 pm

Sorry! Didn't think to view it. Here is my latest spreadsheet
Attachments
Simplified-test.ods
(14.41 KiB) Downloaded 16 times
OpenOffice 4.1.5 on Windows 10
Thracian
 
Posts: 8
Joined: Mon Nov 25, 2019 8:29 pm

Re: Match and verify cell content - Solved!

Postby Thracian » Tue Dec 31, 2019 9:30 pm

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.
Attachments
Simplified-test.ods
(15.26 KiB) Downloaded 17 times
OpenOffice 4.1.5 on Windows 10
Thracian
 
Posts: 8
Joined: Mon Nov 25, 2019 8:29 pm


Return to Calc

Who is online

Users browsing this forum: fiona87 and 21 guests