Hi there! I’m very new to this software and haven’t worked with spreadsheets in a while in general. I’m working on a spreadsheet like this:
I’m trying to find a way to auto populate the cells in each column that have the same name in different rows with what I put in the first instance of that cell. For example, if I have “Red” for four different areas, I would want the other 3 instances of it in the column “bow 1” to auto populate with an “x” if I input that in the first one. Is there a way to do this? Thanks in advance!!
A spreadsheet is not like a paper sheet. Do yourself a favour and create one single table like this:
| Color | Area | Bow |
|---|---|---|
| R | 1 | 1 |
| B | 1 | 1 |
| G | 1 | 1 |
| R | 1 | 2 |
| G | 1 | 2 |
The above sample contains the exact same information as your screen shot.
Add new data to newly inserted cells as new entries come up. The software can do evaluation and aggregation from such a table.
I appreciate your response, but I’m afraid that doesn’t help me much. I’m essentially using this spreadsheet as a checklist for tests I’m running as it’s easier to keep things organized digitally. The “x” is to indicate that each bow is able to hit each color enemy. But many enemies appear in multiple areas and I want to be able to check off the enemy every time it appears once I check it off once if that makes sense. I know it may be an unconventional use of a spreadsheet so I understand if what I’m looking for isn’t actually possible, just thought I’d see what people had to say
Possible approach (version 24.8 needed, or the lox365 extension):
In cell B10 (Based on the screenshot):
=XLOOKUP($A10;$A$3:$A$7;B$3:B$7;"";0;1)
Copying the formula from cell B10 down (to B14) and then right (to C14), should give you an “X” or a zero (0) on the range B10:C14, depending on the content of range B3:C7.
Without extension: IFNA(VLOOKUP($A10;$A$3:$B$7;2;0);"")
Beware that copying such formula to column “C” would need some additional modification. This is why I suggested XLOOKUP() instead. Having said that, using MATCH() and INDEX() should be also a possible alternative too (working for both new and older versions of LO, without needing an extension).
In cell B10:
=IFNA(INDEX(B$3:B$7;MATCH($A10;$A$3:$A$7;0);1);"")
As you can see, using the XLOOKUP() syntax might be simpler. There are always pros and cons.