[Solved] Match and verify cell content
[Solved] Match and verify cell content
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!
Reason: Added ✓. Changed title, was: Match and verify cell content - Solved!
OpenOffice 4.1.5 on Windows 10
Re: match and verify cell content
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.
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.
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.
match and verify cell content
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
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
Re: match and verify cell content
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.
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.
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.
Re: match and verify cell content
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.
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.
Re: match and verify cell content
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
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
Re: match and verify cell content
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.
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.
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.
Re: Match and verify cell content
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
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 101 times
OpenOffice 4.1.5 on Windows 10
Re: Match and verify cell content
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.
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
Re: Match and verify cell content
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 80 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Match and verify cell content
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!
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
Re: Match and verify cell content - Solved!
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Match and verify cell content - Solved!
Sorry! Didn't think to view it. Here is my latest spreadsheet
- Attachments
-
- Simplified-test.ods
- (14.41 KiB) Downloaded 84 times
OpenOffice 4.1.5 on Windows 10
Re: Match and verify cell content - Solved!
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 91 times
OpenOffice 4.1.5 on Windows 10