Incrementing a column number within formula

0
votes

Here is my formula: 

{=MAX(ROW($1:$120)*(Table1[results1518]=1))}

so far I have done 1518 cells manually as indicated by the results#.

I have about 2000 more cells to go. The table is on one sheet and this formula in each cell on another.

I need to find a way to increment the results# from one cell down to the next cell below it and so on down the column on the second sheet.

1
excelexcel-formulaoffset

1 Answers

0
votes

I would make a helper column that listed all the result# you needed to cycle through. You can either use excel's auto fill ability or just add 1 to the value of cell in the row above. For example, A3=A2+1.

Next create a second helper column that returned a text value embeding that number. B2 would look something like this ="Table1[results"&A2&"]"

Finally, you can use Excel's indriect formula looks up the value specified by the second helper column. {=MAX(ROW($1:$120)*(INDIRECT(B2)=1))}

There are probabally more effecient methoods, but without knowing more about your setup, this is what I've got.