[Solved] Auto-fill for Adjacent Cells with Same Text
[Solved] Auto-fill for Adjacent Cells with Same Text
Hi there,
I tried searching for this in the forums but couldn't find what I was looking for. Sorry if it seems like an easy question!
I have a spreadsheet with a list of names in one column, and numbers in the column to the right of them. The names contain many, many duplicates, and every time the name is the same, the corresponding number is the same. This number changes around daily, so I have to go through and manually change the number for each name every day, which takes a lot of time. There would be 2 ways to speed this up, though I can't figure out how to do either of them.
1. A "find & replace" function that can find the content of one cell and change value the adjacent cell, that can "replace all."
2. In the different part of the sheet, have a list of the names in a column (but only with the names listed once) and then the value in an adjacent column, and when that column is changed, it changes the value next all of the other names in the original columns that I discussed above. So, like a "Master" column that when edited fills in the rest.
#2 seems more probable, and I'd love to know how to do this.
Thank you so much!
Using OS X 10.7.5
I tried searching for this in the forums but couldn't find what I was looking for. Sorry if it seems like an easy question!
I have a spreadsheet with a list of names in one column, and numbers in the column to the right of them. The names contain many, many duplicates, and every time the name is the same, the corresponding number is the same. This number changes around daily, so I have to go through and manually change the number for each name every day, which takes a lot of time. There would be 2 ways to speed this up, though I can't figure out how to do either of them.
1. A "find & replace" function that can find the content of one cell and change value the adjacent cell, that can "replace all."
2. In the different part of the sheet, have a list of the names in a column (but only with the names listed once) and then the value in an adjacent column, and when that column is changed, it changes the value next all of the other names in the original columns that I discussed above. So, like a "Master" column that when edited fills in the rest.
#2 seems more probable, and I'd love to know how to do this.
Thank you so much!
Using OS X 10.7.5
Last edited by jglag on Wed May 13, 2015 9:46 pm, edited 1 time in total.
OpenOffice 4.1.0
- MrProgrammer
- Moderator
- Posts: 5280
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Auto-fill for Adjacent Cells with Same Text
Both methods are demonstrated on the attached spreadsheet. #1 is performed with Data → Filter → AutoFilter. #2 is performed with VLOOKUP. Method 2 will be much less work once you create the auxillary table.jglag wrote:1. A "find & replace" function that can find the content of one cell and change value the adjacent cell, that can "replace all."
2. In the different part of the sheet, have a list of the names in a column (but only with the names listed once) and then the value in an adjacent column, and when that column is changed, it changes the value next all of the other names in the original columns that I discussed above.
You can do the initial creation of that table using Data → Pivot Table (formerly Data Pilot). Read about that in the Help, in this forum, or in the Wiki. Put the names in the Row Fields area. Use the AVERAGE function to summarize the number column in the Data Fields area of the pivot table.
If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
- Attachments
-
- 201505131245.ods
- (7.89 KiB) Downloaded 378 times
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.7.6, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.7.6, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Auto-fill for Adjacent Cells with Same Text
Thank you very much!MrProgrammer wrote:Both methods are demonstrated on the attached spreadsheet. #1 is performed with Data → Filter → AutoFilter. #2 is performed with VLOOKUP. Method 2 will be much less work once you create the auxillary table.jglag wrote:1. A "find & replace" function that can find the content of one cell and change value the adjacent cell, that can "replace all."
2. In the different part of the sheet, have a list of the names in a column (but only with the names listed once) and then the value in an adjacent column, and when that column is changed, it changes the value next all of the other names in the original columns that I discussed above.
You can do the initial creation of that table using Data → Pivot Table (formerly Data Pilot). Read about that in the Help, in this forum, or in the Wiki. Put the names in the Row Fields area. Use the AVERAGE function to summarize the number column in the Data Fields area of the pivot table.
If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
I am trying to use the 2nd method, but I am encountering a problem. It works for the first time, but then when I try to copy the formula for the cell below, it says #N/A, even if I change the cell it's pulling from. The array it's pulling from is the same.
Thoughts?
OpenOffice 4.1.0
Re: Auto-fill for Adjacent Cells with Same Text
In your spreadsheet when you select and drag down, OR manually change the number of the cell it's pulling form (ex: A1 to A2), it works, but when I do it in my spreadsheet, it says #N/A when I make the same change, regardless.
Thanks.
Thanks.
OpenOffice 4.1.0
Re: Auto-fill for Adjacent Cells with Same Text
UPDATE:
This is extremely bizarre. When I drag the formula down, it works correctly for SOME cells, and for others it says #N/A. I thought it had something to do with spelling, but it doesn't seem to. I figured I'd go through and "ignore all" spelling mistakes in spellcheck. It still doesn't work. I'm 100% sure that everything is spelled correctly and that the names are all spelled the same way, from both tables.
What a strange and confusing situation. Any ideas...?

This is extremely bizarre. When I drag the formula down, it works correctly for SOME cells, and for others it says #N/A. I thought it had something to do with spelling, but it doesn't seem to. I figured I'd go through and "ignore all" spelling mistakes in spellcheck. It still doesn't work. I'm 100% sure that everything is spelled correctly and that the names are all spelled the same way, from both tables.
What a strange and confusing situation. Any ideas...?


OpenOffice 4.1.0
- MrProgrammer
- Moderator
- Posts: 5280
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Auto-fill for Adjacent Cells with Same Text
jglag wrote:When I drag the formula down, it works correctly for SOME cells, and for others it says #N/A.
You are using VLOOKUP but didn't read the tutorial that I linked for you in my earlier post, right? Study the tutorial before attempting to use the function. Your situation is almost certainly covered there. You might also want to read [Tutorial] Ten concepts that every Calc user should know. Spending ten minutes reading that might easily save you ten hours of frustration.[Tutorial] VLOOKUP questions and answers wrote:If your VLOOKUP function doesn't work the way you expect, consider that there are many possible difficulties (especially Q5, Q6, Q11, Q13, Q14, Q16, Q17, and Q19). If you're tried the solutions here and are still stumped, when creating a new topic in the Calc section be sure to attach your document (not a picture of it). Otherwise it is hard for the volunteers to help you because in most cases a picture doesn't have enough information to diagnose the problem.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.7.6, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.7.6, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Auto-fill for Adjacent Cells with Same Text
Hi there,
I read the online documentation and followed your spreadsheet at carefully as possible, but it still confuses me. I'm not quite sure what I'm doing wrong.
Here I have attached my spreadsheet. Let me explain where this is all coming from:
Sheet: Formula Test (The last sheet)
Names go from B3 to B18, and corresponding value is in the column to the right, under "Raw Points." Please disregard the "final points" column.
The name table I am drawing my VLOOKUP from starts at R2 on that same sheet.
Thank you very much for any help.
I read the online documentation and followed your spreadsheet at carefully as possible, but it still confuses me. I'm not quite sure what I'm doing wrong.
Here I have attached my spreadsheet. Let me explain where this is all coming from:
Sheet: Formula Test (The last sheet)
Names go from B3 to B18, and corresponding value is in the column to the right, under "Raw Points." Please disregard the "final points" column.
The name table I am drawing my VLOOKUP from starts at R2 on that same sheet.
Thank you very much for any help.
Last edited by jglag on Wed May 13, 2015 9:46 pm, edited 1 time in total.
OpenOffice 4.1.0
Re: Auto-fill for Adjacent Cells with Same Text
I see only one #N/A.
Have a look and you may see why.
You should also read the part of the tutorial about absolute and relative reference.
Have a look and you may see why.
You should also read the part of the tutorial about absolute and relative reference.
LibreOffice 6.4.5 on Windows 10
Re: Auto-fill for Adjacent Cells with Same Text
When I copy the formula, even in batch, how do I make it search form the same table, and not move everything it's searching for down? I'd like it to be able to be dragged correctly.
I will read through everything and gather as much knowledge on the intricacies of this software as soon as I can. I'm sorry for not being 100% well-read in all the details. It's just I run a fairly high-profile fantasy tennis league and we are going under a switch in score calculation, and I need to get this finished very quickly. I appreciate all the help you guys are giving me given how quickly I need this up and working.
Thank you so much.
I will read through everything and gather as much knowledge on the intricacies of this software as soon as I can. I'm sorry for not being 100% well-read in all the details. It's just I run a fairly high-profile fantasy tennis league and we are going under a switch in score calculation, and I need to get this finished very quickly. I appreciate all the help you guys are giving me given how quickly I need this up and working.
Thank you so much.
OpenOffice 4.1.0
Re: Auto-fill for Adjacent Cells with Same Text
=VLOOKUP(B3;R2:S81;2;0) reference is relative and change when dragging.jglag wrote:When I copy the formula, even in batch, how do I make it search form the same table, and not move everything it's searching for down? I'd like it to be able to be dragged correctly.
=VLOOKUP(B3;$R$2:$S$81;2;0) reference of the table is absolute and don't change.
LibreOffice 6.4.5 on Windows 10
Re: Auto-fill for Adjacent Cells with Same Text
You're wonderful, thank you so much. Have a great day.gerard24 wrote:=VLOOKUP(B3;R2:S81;2;0) reference is relative and change when dragging.jglag wrote:When I copy the formula, even in batch, how do I make it search form the same table, and not move everything it's searching for down? I'd like it to be able to be dragged correctly.
=VLOOKUP(B3;$R$2:$S$81;2;0) reference of the table is absolute and don't change.
OpenOffice 4.1.0