I have the following formula that currently works in my spreadsheet where I have a row of #'s that I need to return the one to the furthest right (new #'s get inserted into the range on a regular basis). The formula is =LOOKUP(9^99,46:46).

I now need to flip the formula so that it returns the # furthest to the left in row 46.

I'm not sure if I am explaining it right, but basically I need a cell to = say I46....where eventually new data will get inserted into column I and the # that was in I46 will get pushed to O46, but the formula needs to stay stuck on I46 and return the new value in I46.

Any help on this would be greatly appreciated. If I didn't explain this right or more info is needed, let me know.

Thanks!