# Thread: automatically update value in formula to last entry

1. ## automatically update value in formula to last entry

How do I incorporate the return of the vlookup function
=VLOOKUP(9.99999999999999E+307,A:A,1)

with the formula:
=IF(TODAY()-A7>0,TODAY()-A7,"0")

where the VLOOKUP return will replace A7 in above formula with the last entry value
in "B".

In other words, if I enter 3/20/2011 in "A8", I want the "A7" in =IF(TODAY()-A7>0,TODAY()-A7,"0")
to be updated to value of "A8" or 3/20/2011
and then the return of "D3" would be
=IF(TODAY()-A8>0,TODAY()-A8,"0")

------
What advantage would the following approach in determining value of last entry have, if any.

=INDIRECT("A"&MAX(ROW(1:65535)*(A1:B65535<>"")))

2. =MAX(0,TODAY()-LOOKUP(9E+300,A:A))

Avoid INDIRECT generally as it's a volatile function. Not usually a big deal if you only have one or two such formulas, but a good habit to get into, IMO.

3. ## The Following User Says Thank You to rory For This Useful Post:

skipro (2011-03-23)

4. Rory,
As usual, works as needed. Thanks.

How is 9E+300 used and mean? I have seen it's use but I cannot find it's value or meaning. My understanding is the value located here needs to represent a number larger than any it will find in its search so that the formula will settle on the last entry, correct?
But what does it represent and how?
How does it differ from: (9.99999999999999E+307
Why use one over the other?

5. 9E+300 = 9 * 10 ^ 300 which is a very large number, certainly larger than any date you'll be using! 9.99999999E+307 is larger still, but I'm too lazy to type all that!

6. What does the "MAX(0" do in
=MAX(0,TODAY()-LOOKUP(9E+300,A:A))

Why not "=(TODAY()-LOOKUP(9E+300,A:A))" ?

7. It replaces the IF part of your formula - in other words if the lookup value is after today, return 0

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•