CODEWITHSUNDEEP
excelexcel-formula
Tom Nulty
Tom Nulty

Reputation: 97

Edit excel autofill options

I have a very confusing and probably very inefficient formula here: 

=INDEX(Sheet1!A2:A4,MATCH(A4,INDIRECT("Sheet1!" & CHAR(64 + MATCH(C1,Sheet1!A1:G1,0)) & 2):INDIRECT("Sheet1!" & CHAR(64 + MATCH(C1,Sheet1!A1:G1,0)) & 3),0))

I need this to be auto filled into multiple cells, at the moment it increments all of the cells in the formula. Is there a way to just increment the cells in the formula that are on the current sheet and leave the ones from Sheet 1? E.G:

=INDEX(Sheet1!A2:A4,MATCH(A5,INDIRECT("Sheet1!" & CHAR(64 + MATCH(D1,Sheet1!A1:G1,0)) & 2):INDIRECT("Sheet1!" & CHAR(64 + MATCH(D1,Sheet1!A1:G1,0)) & 3),0))

Upvotes: 1

Views: 61

Answers (1)

henrythedj
henrythedj

Reputation: 78

Per your comment: if you mean the C$1 'row number,' you would just remove the $, leaving C1. When you autofill up to down, this will give you C1, C2, C3, etc. and when you autofill left to right you will get C1, D1, E1, etc.

You can then autofill (up to down) from the D1, E1 to obtain D2, E2.

Upvotes: 1

Related Questions