CODEWITHSUNDEEP
excelms-officeexcel-formulavba
user1455116
user1455116

Reputation: 2144

Auto increment row number in excel sheet

I have the following formula written in the cell B5

=SUMPRODUCT(--(COLORINDEXOFRANGE(Data!D10:HO10,FALSE,1)=COLORINDEXOFONECELL(Data!$F$3,FALSE,1)))

When i drag down from B5 to the next row, the cells in the formula change to COLORINDEXOFRANGE(Data!D11:HO11,FALSE,1)

But i want the column number to remain the same and want just the row number to change to, for example COLORINDEXOFRANGE(Data!E10:HO10,FALSE,1) for the cell C5 and COLORINDEXOFRANGE(Data!F10:HO10,FALSE,1) for cell D5 and so on.

COLORINDEXOFRANGE is an inbuilt function that i found on the internet.

Please help. Thanks

Upvotes: 3

Views: 28811

Answers (4)

David Mitten
David Mitten

Reputation: 31

I had a similar problem: dragging a value down, didn't increment as expected. The problem was that I had filters applied to the data. Removing the filter restored the functionality.

Upvotes: 0

barry houdini
barry houdini

Reputation: 46341

If the end of the range remains the same then you could use INDEX to change the start of the range only, i.e. replace Data!D10:HO10 with

=INDEX(Data!D$10:HO$10,ROWS(B$5:B5)):Data!HO$10

INDEX formulas of this type are only semi-volatile (they re-calculate when sheet is opened) which is perhaps preferable to fully volatile OFFSET and INDIRECT functions. Also ROWS function is better than ROW as it will cope with rows being inserted (or deleted) above the formula without changing the result

Upvotes: 3

SeanC
SeanC

Reputation: 15923

to change from a horizontal to a vertical reference, I have used OFFSET, where I reference the values in another sheet using =OFFSET('Forecast'!$I$17,COLUMN()-3,0) so that the column() of the reference going across becomes the row reference

Upvotes: 1

klonq
klonq

Reputation: 3587

There are a few ways to get around this dragging a dropping formulas to fixed references cells :

  1. To answer your question literally you can reference they current row number using ROW(), so ROW() - 1 gives the previous row number.
  2. But this isn't going to work in your case because you are referencing a range. Another function is INDIRECT(), have a look at the documentation, but this could be used as such INDIRECT("R5C" & COLUMN(), FALSE)
  3. Because option #2 tends to make formulas very long and (almost) unreadable sometimes you can get away with a named range

Upvotes: 1

Related Questions