CODEWITHSUNDEEP
excelexcel-formula
MoralesJosue
MoralesJosue

Reputation: 199

Drag-down columns in formula

I want to drag this formula down to other cells and want to change B$1:B$1000 to C$1:C$1000 (and so on...) but when it is dragged down, it just remains the same. I have read other threads similar to this one and tried their solutions but with no luck.

=INDEX(OFFSET('[1-  Abril  (Captura de datos).xlsx]Moldeo'!B$1:B$1000,COLUMN()-1,0),MATCH($C$1384&$K$1385,'[1-  Abril  (Captura de datos).xlsx]Moldeo'!$A:$A,1))

Upvotes: 2

Views: 152

Answers (2)

user4039065
user4039065

Reputation:

It seems that you've mixed and mismatched the OFFSET function. You have put COLUMN()-1 into the parameter for the rows. In fact, you need to put ROW(1:1)-1 into the columns parameter.

=INDEX(OFFSET('[1-  Abril  (Captura de datos).xlsx]Moldeo'!B$1:B$1000, 0, ROW(1:1)-1), MATCH($C$1384&$K$1385, '[1-  Abril  (Captura de datos).xlsx]Moldeo'!$A:$A, 1))

ROW(1:1)-1 will progress from 0, 1, 2, 3... as you fill down. With this in the columns parameter, it will offset one column to the right for each row you fill down.

Upvotes: 2

Marcel
Marcel

Reputation: 2794

actually first you need to drag it to the right and once it is finished, select the outcome cells and copy them and then paste special and select transpose.

Upvotes: 2

Related Questions