Referencing data that changes

[karen7459]

Hello,
I have a workbook with 5 sheets. The first one is a summary of the other 4. I'd like to be able to grab the last value of a running total and show it on the summary sheet. In other words, each sheet is an account with a running balance and I'd like the last balance to show on the summary page so I can see all the balance totals in one place. Can someone please help me? I am a novice at this so please bear with me!
Karen

[BadlySpelledBuoy]

Hi Karen and welcome.

Any chance we could see an example workbook so we could see the actual data layout?

BSB

[Tony Valko]

When you say "last balance" do you mean the last (bottom-most) numeric entry in a column?

If so, try something like this...

=LOOKUP(1E100,Sheet2!A:A)

That will return the last (bottom-most) numeric entry from Sheet2 column A.

[karen7459]

Tony Valko - that is EXACTLY what I was looking to do! Thank you so much! Can you tell me what the "1E100" means? Also, what if there is a formula in the cell where the LOOKUP is looking? Does that make sense?

[FDibbins]

Karen, welcome to the forum

1E100 is a really large number( largest?) that there is for excel, so lookup will search for that, and if not found, will search for the next lowest number above that

Also, if you have a workbook (excel file) you should provide a sample of that in excel, not a word document

[karen7459]

Thank you FDibbins. My other question is what happens when there is a formula already in a cell and the info in that cell is the number I need to lookup?

[Tony Valko]

1E100 is scientific notation for the very large number 1 followed by 100 0s.

Scientific notation is a "shorthand" method of expressing vey long numbers.

The way that LOOKUP works in this application is that if every number in the range is less than the lookup value (1E100) then the formula will return the last numeric value from the range.

We use the gigantic number 1E100 as the lookup value to ensure that we get the correct result.

There is a 100% chance that the numbers you're working with are nowhere near the value of 1E100.

My other question is what happens when there is a formula already in a cell and the info in that cell is the number I need to lookup?

What do you want to do once you find that number?

[karen7459]

Thanks for all the great info! I was able to get my workbook to do exactly what I wanted!

[Tony Valko]

Good deal. Thanks for the feedback!