How can display multiple values to single column?

Display most recent value entered in column

• QUESTION: I have created an account balance sheet in Excel 2007. I enter transactions that have occurred on my debit card and it calculates my account balance for me. However, I would like to display the most recent balance on a separate sheet automatically, as a form of title page telling me my account balance. I do not know how to do that. I will attach an image of the account transactions sheet. I cannot attach two photos or I would show you the first sheet titled "Account Balance & Overview". There is an area on the "Account Balance & Overview" sheet that I want the most recent balance from the "Transactions List" sheet automatically shown. Please help me. It would be so nice if this could work for I have otherspreadsheets I would like to apply this same idea to. The balance column goes through till G2000 and starts at G4. ANSWER: Morgan, in the cell where you want to see the latest account balance in your Overview sheet, put in the formula =LOOKUP(9.99999999999999E+307,'Transactions List'!$G$4:$G$2000) If there will be a period when you have no values in G4:G2000 you can do =ifError(LOOKUP(9.99999999999999E+307,'Transactions List'!$G$4:$G$2000),0) If you have any problems with the formula, make sure I typed in the Sheet name correctly. ---------- FOLLOW-UP ---------- QUESTION: Awesome! That worked perfect! Thank you so much! However, I would like to ask how that works. You don't have to explain it but it would be so advantageous for me if I understood how that formula worked. Morgan

• Answer:

  Morgan, form: LOOKUP(lookup_value, array) Lookup looks up the lookup_value in the first row or column of the array. In normal usage, array would usually be a rectangular area with multiple rows and columns. Lookup will look for the Lookup_value in the first vector of the array based on whether there are more rows than columns (looks in the first column) or the array is square or has more columns than rows (looks in the first row). It assumes the values in the lookup vector are in ascending order It then returns the corresponding number/value from the last vector in the array. If the lookup_value is not found, it returns the last number that is less than the lookup_value. That is the normal behavior. In your case, the array is a single column. So it looks up in that column and returns from that column. Now you data isn't sorted - but lookup assumes it is. So we search (lookup_value) for the largest possible number thatcan repesent which is the 9.99999999999999E+307. That guarantees that the number (lookup_value) won't be found. The lookup function then searches back in the array/column from the last number in the array until it finds a number that is less than the lookup_value - that number will always be the last number in the array since every number in the array is less than the lookup_value. So the last number in the array is the current account balance and that is what is returned. Hope that explains it for you.