google-sheetsspreadsheet

Drag and copy range vertically from spreadsheet google


I want the range A:A changes to B:B , C:C when i try to drag & copy a formula vertically.

The only way i know how i could do this is by changing the formula manually but i don't want to do this because i have around 700 rows

Formula i'm using are ranges from another page

enter image description here

I want to copy the formula in many rows, but as you can see on the image i get the formula like in the left column. I want to achieve something like in the right column without having to modify the range manually. (In this example i removed the = to show you the formula)

I tried excel way but it doesn't work by trying to create 2 line one with A:A and second line with B:B and than select both lines and drag to copy lower lines but it just keeps on copy both ranges over and over.


Solution

  • Try this:

    =MATCH("*"&$B$2&"*",INDIRECT("Data!"&CHAR(64+row(A2))&":"&CHAR(64+row(A2))),0)
    

    CHAR(64+row(A2)) is the same as CHAR(66) which is B, as you drop it down, Char() increases by one so you get the next column. So CHAR(64+row(A3)) is the same as CHAR(67) which is C