Example Image

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):

Expected Output

  • Could you pass more infos like what are contents for those columns and the expected Output, will help us to fix the issue!
    – Rajesh S
    Mar 27, 2019 at 5:28
  • Kindly refer to my linked image for a visual example of the data in row A. I have added a second image to better demonstrate the expected output.
    – phLOx
    Mar 27, 2019 at 7:07

2 Answers 2

  • This solved my problem without creating a target column for INDIRECT, which is exactly what I needed. Thank you, I will attempt to learn ADDRESS for future use.
    – phLOx
    Mar 27, 2019 at 8:00
  • What would be the Formula if Location changes like if Names are in Column L to T !
    – Rajesh S
    Mar 27, 2019 at 8:49



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.