INDEX function to stay on it's current column if more are added

[KranerRyan]

I'm using =INDEX(Jan!$2:$2,(ROW(E2)-1)*5+36) to pull the date from Column 41, Row 2. When I add a new column it still pulls from the 41st although the data I want moved to the 42nd... I am making/formatting a sheet and the number of columns keeps changing. Getting to be a pain updating the 36 manually to keep seeing the data. Any workarounds?

Thanks!

 

[JonXL]

Try replacing the 36 in your formula with COLUMN(AJ1).

 

[KranerRyan]

Returns #Name ?.

 

[KranerRyan]

However you put COLUMN(AJ1) into a cell and then reference that, it will update with new columns. Thanks!

 

[JonXL]

Glad you got it working. I've never used the function, I just know of it. However, I find it strange that it wouldn't work inside the full formula - I've never heard of a function with such a limitation. Typically, whenever you reference a cell containing a formula, you can get the same result by putting the referenced formula directly into the referring cell.