Auto increment row number in excel sheet

3
votes

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

4
excelms-officeexcel-formulavba
I'm confused. Is the behavior you want not the built in default behavior? Can you not achieve what you want by using $ in the formulas? – Brad
@Brad i tried to use $, but when we drag down, the Row number increments, for example D10 changes to D11 but not to E10. As Sean suggested below, i need to use OFFSET but i was not able to figure out where to write 'OFFSET' in my formula – user1455116
Do you want to sort of reverse the incrementing? dragging vertically would normally increment the rows property but you want to instead keep your row but increment the column. and vice versa for horizontal dragging, you want vertical offsetting? Kind of as through you had transposed? – Brad

4 Answers

3
votes

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

1
votes

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
1
votes

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

0
votes

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.