Tracking price history with left column latest price

**Pish** · 07-14-2012, 06:54 PM

I just couldn't find anything similar in the search so I thought I would try here. It's fairly straightforward though but I have little experience with formulas so please explain thoroughly. Here is my example:

A B C D E F G H
1 10.34 15.27 78.00 64.00

So what I am trying to do is track prices over time. I input the price each day and what I would like to happen is to have A1 show the latest price which in this case is E1 but will change every day as I enter numbers moving to the right. Being terrible at formulas I was just thinking maybe I could use zeros in the blank spaces F, G, H and so on and then right a formula whereby if the cell has a number and there is a zero in the cell to the right, then post that number in A1.

Hope you undersand what I am trying to do. Simple but confusing for me. Many thanks in advance.

**benishiryo** · 07-14-2012, 10:10 PM

hi Pish, welcome to the forum. you do not have to put zeros. copy this formula & place it in the formula bar:
=INDEX(B2:H2,1,MAX(COLUMN(B2:H2)*(B2:H2<>""))-1)

press CTRL + SHIFT + ENTER instead of just ENTER.

if your range exceeds H, just change all the "H2" in the formula.

**Pish** · 07-14-2012, 11:54 PM

Thank you very very much for replying to my request. I did try inserting this formula and since you did not reference where to insert it, I just assumed it would be B2. After pasting, I used the CTRL+SHIFT+ENTER and received the following,

"Microsoft Office Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you. Try editing the last formula you entered or removing it with the Undo command."

Any suggestions from here?