In the above image fields are populated as follow:
- A1 is empty/not important
- B1 = animal
- C1 = dog
- D1 = male
- E1 = animal
- F1 = cat
- G1 = male
- H1= insect
- I1 = ladybug
- J1 = unknown
I am working in column A, and what I wish to achieve is that column A should be equal to the name of the creatures in row 1. For example, A2 should equal "dog", A3 should equal "cat", A4 should equal "ladybug", etc. etc.
The name of the creature will always be offset by 3 cells in the row. I require them listed without empty rows beneath each other in column A. I cannot delete rows at any time, therefore I need the formula to do the work for me.
Currently, if in A2 and A3 I point respectively to C1 and F1, and I attempt to click and drag the formula down column A, Google Sheets (as well as Excel) has no idea what my intentions are, which results in the next cell pointing to column C and the first empty row number I drag it onto, and then repeats that cycle all the way down.
I have attempted the
OFFSET function, however, it suffers from the same ailment of populating "in the wrong direction" and one at a time.
My last resort would be to create an
INDIRECT column with the correct pointers, but this is not favorable.
While I would like to stay away from Macros if at all possible, I will consider it a second-to-last resort if there truly is no other way.
Any ideas greatly appreciated.
EDIT (Expected Output):