Changing which cell a formula points to automatically??

[heytherejem]

I've been asked to help a colleague and i'm stumped.

I have this set of financial data and the total column points to the latest value in that row.
The next month it needs to point to the next column along in that row.

You can't change the top one and drag down, because that changes the row not the column.

Is there any way to perform this without re-pointing to the new cell manually for each row?

Attached example - so I need Column BE to point to the yellow cell.

[Aardigspook]

You ask for column BE to point to the yellow cell, but the data you've got there is for the cell(s) to the left of the yellow ones.

If you want it to point to the yellow cells, use this in BE2, dragged down:

Please Login or Register  to view this content.

If you want to show the data you've already got, then use this, dragged down:

Please Login or Register  to view this content.

You'll eventually get two incorrect results when the column reference ends up pointing to columns B then A, then you'll get #REF errors. Those are fixable with IF/IFERROR statements - let me know if you want that too.

[eemiller1997]

I was able to get this formula to work when I removed the 0's out of the empty rows, as this formula was looking for the cell before the first empty one.

Please Login or Register  to view this content.

The cells can be formulated to be empty if there is a zero using an IF formula.

[heytherejem]

Hi there,

Yes I want column BE to point to the blank yellow cells, as they are currently pointing to the populated cell to it's left. The yellow cell will soon be showing data.

I've pasted your formula in, but it's not working - it looks like it's referencing a couple of cells above and below it. I've re-attached the data and your formula is in the pink cells - you can see what it's pointing at.

Thanks,
Jemma

[heytherejem]

@eemiller1997 - this worked! Thank you so much. That will save my friend hours probably

[Aardigspook]

Glad you got an answer which worked for you - and thanks for marking the thread Solved.

Not sure why my (first) formula didn't work for you - it seems fine to me, as it returns what's in the yellow cells. The reason it seems to be referencing cells above/below is that I've used Row() in the 'column' part of the Offset formula to get the column to increment backwards as you drag down the rows.

However, since you're happy with eemiller1997's solution, no need to look into mine further .