formula for excel

Copper Contributor

I have money amounts in a column that are entered daily. I.E. A1 100000, A2 101000 etc.. I want to be able to automatically transfer the amount entered that day to a another cell I.E. B4. So as I enter an amount in A1 in goes to B4, the next day A2 now shows in B4 and so on. I've been trying the offset function but without success. Can anyone help?

6 Replies

@Robm1952 

=INDEX(A1:A19,LARGE(IF(A1:A19<>"",ROW(1:19)),1))

 

Is this what you are looking for? Enter formula as arrayformula with ctrl+shift+enter if you don't work with Office365 or 2021.

Hi Thanks so much for the quick response, Its just returning zero. Sorry not very advanced with excel. working with excel 2013

@Robm1952 

I work with Excel 2013 as well. It works in my attached spreadsheet.

Not sure I understand I see 18 in the second column. So you moved 18 from the bottom of the 1st column over to the second column? Did it work that way with the numbers above?

@Robm1952 

The formula in cell B4 displays the 18 from cell A8 in cell B4.

If you enter for example 107 in cell A9, the formula in B4 will show 107 in B4.

If you then enter for example 274 in cell A10, the formula in B4 will show 274 in B4.

 

The same was done when i entered the numbers in cells A1:A7.

 

OK so I entered it exactly how you said except I changed the A to B because that's where the columns reside. Confused about the arrayformula reference.