VLOOKUP using a cell calculated with NOW returns Error

Don't use NOW. Try TODAY().
************
Anne Troy
www.OfficeArticles.com



"Chris Berding" <[email protected]> wrote in message
news:[email protected]...
>I am calculating the present age of people, then by their age, I am looking
> them up in a rate chart based on their age.
>
> I calculate their age by NOW-D.O.B. which works fine. (see actual cell
> contents below)
>
> Then, I use the cell that contains the age result in a VLOOKUP cell to
> determine their rate. It works perfectly for exactly 3 people, then it
> returns an error #REF! because, I have deduced, that it doesn't like the
> fact
> that the age is constantly changing each time the calculation is done.
>
> Why does it work for 3 people and then stop? More importantly, is there a
> way i can just take the "value" of the age cell into the VLOOKUP, rather
> than
> pulling the dynamic number into the function?
>
> Here's my VLOOKUP just for reference:
> =(VLOOKUP(F3,VLOOKUPrates!$B$2:$E$74,VLOOKUPrates!$B$2:$B$74))*M3
> where F3 is the calculated AGE and M3 is a constant.
>
> Here's my present AGE calculation for reference (that is somehow
> considered
> in the above VLOOKUP):
> =IF(TRUNC(((YEAR($A$1)-YEAR(E3))*12+MONTH($A$1)-MONTH(E3))/12)=105, "
> ",TRUNC(((YEAR($A$1)-YEAR(E3))*12+MONTH($A$1)-MONTH(E3))/12))
> Where $A$1 = NOW
>
> any help gratefully acknowleged and appreciated !

On Sat, 20 Aug 2005 18:40:01 -0700, "Chris Berding"
<[email protected]> wrote:

>I am calculating the present age of people, then by their age, I am looking
>them up in a rate chart based on their age.
>
>I calculate their age by NOW-D.O.B. which works fine. (see actual cell
>contents below)
>
>Then, I use the cell that contains the age result in a VLOOKUP cell to
>determine their rate. It works perfectly for exactly 3 people, then it
>returns an error #REF! because, I have deduced, that it doesn't like the fact
>that the age is constantly changing each time the calculation is done.
>
>Why does it work for 3 people and then stop? More importantly, is there a
>way i can just take the "value" of the age cell into the VLOOKUP, rather than
>pulling the dynamic number into the function?
>
>Here's my VLOOKUP just for reference:
>=(VLOOKUP(F3,VLOOKUPrates!$B$2:$E$74,VLOOKUPrates!$B$2:$B$74))*M3
>where F3 is the calculated AGE and M3 is a constant.
>
>Here's my present AGE calculation for reference (that is somehow considered
>in the above VLOOKUP):
>=IF(TRUNC(((YEAR($A$1)-YEAR(E3))*12+MONTH($A$1)-MONTH(E3))/12)=105, "
>",TRUNC(((YEAR($A$1)-YEAR(E3))*12+MONTH($A$1)-MONTH(E3))/12))
>Where $A$1 = NOW
>
>any help gratefully acknowleged and appreciated !

A few suggestions.

1. To calculate age, use the simpler (but mostly undocumented in Excel)
formula:

=IF(OR(E3="",E3>TODAY()),"",DATEDIF(E3,TODAY(),"y"))

or, with A1: =TODAY()

=IF(OR(E3="",E3>A1),"",DATEDIF(E3,A1,"y"))

2. The syntax of your VLOOKUP is incorrect. The third argument is supposed to
be a column number within your table. You have:

=(VLOOKUP(F3,VLOOKUPrates!$B$2:$E$74,VLOOKUPrates!$B$2:$B$74))*M3

But perhaps:

VLOOKUP(F3,VLOOKUPRates!$B$2:$E$74,4)*M3


--ron

I am calculating the present age of people, then by their age, I am looking
them up in a rate chart based on their age.

I calculate their age by NOW-D.O.B. which works fine. (see actual cell
contents below)

Then, I use the cell that contains the age result in a VLOOKUP cell to
determine their rate. It works perfectly for exactly 3 people, then it
returns an error #REF! because, I have deduced, that it doesn't like the fact
that the age is constantly changing each time the calculation is done.

Why does it work for 3 people and then stop? More importantly, is there a
way i can just take the "value" of the age cell into the VLOOKUP, rather than
pulling the dynamic number into the function?

Here's my VLOOKUP just for reference:
=(VLOOKUP(F3,VLOOKUPrates!$B$2:$E$74,VLOOKUPrates!$B$2:$B$74))*M3
where F3 is the calculated AGE and M3 is a constant.

Here's my present AGE calculation for reference (that is somehow considered
in the above VLOOKUP):
=IF(TRUNC(((YEAR($A$1)-YEAR(E3))*12+MONTH($A$1)-MONTH(E3))/12)=105, "
",TRUNC(((YEAR($A$1)-YEAR(E3))*12+MONTH($A$1)-MONTH(E3))/12))
Where $A$1 = NOW

any help gratefully acknowleged and appreciated !

Don't use NOW. Try TODAY().
************
Anne Troy
www.OfficeArticles.com



"Chris Berding" <[email protected]> wrote in message
news:[email protected]...
>I am calculating the present age of people, then by their age, I am looking
> them up in a rate chart based on their age.
>
> I calculate their age by NOW-D.O.B. which works fine. (see actual cell
> contents below)
>
> Then, I use the cell that contains the age result in a VLOOKUP cell to
> determine their rate. It works perfectly for exactly 3 people, then it
> returns an error #REF! because, I have deduced, that it doesn't like the
> fact
> that the age is constantly changing each time the calculation is done.
>
> Why does it work for 3 people and then stop? More importantly, is there a
> way i can just take the "value" of the age cell into the VLOOKUP, rather
> than
> pulling the dynamic number into the function?
>
> Here's my VLOOKUP just for reference:
> =(VLOOKUP(F3,VLOOKUPrates!$B$2:$E$74,VLOOKUPrates!$B$2:$B$74))*M3
> where F3 is the calculated AGE and M3 is a constant.
>
> Here's my present AGE calculation for reference (that is somehow
> considered
> in the above VLOOKUP):
> =IF(TRUNC(((YEAR($A$1)-YEAR(E3))*12+MONTH($A$1)-MONTH(E3))/12)=105, "
> ",TRUNC(((YEAR($A$1)-YEAR(E3))*12+MONTH($A$1)-MONTH(E3))/12))
> Where $A$1 = NOW
>
> any help gratefully acknowleged and appreciated !

On Sat, 20 Aug 2005 18:40:01 -0700, "Chris Berding"
<[email protected]> wrote:

>I am calculating the present age of people, then by their age, I am looking
>them up in a rate chart based on their age.
>
>I calculate their age by NOW-D.O.B. which works fine. (see actual cell
>contents below)
>
>Then, I use the cell that contains the age result in a VLOOKUP cell to
>determine their rate. It works perfectly for exactly 3 people, then it
>returns an error #REF! because, I have deduced, that it doesn't like the fact
>that the age is constantly changing each time the calculation is done.
>
>Why does it work for 3 people and then stop? More importantly, is there a
>way i can just take the "value" of the age cell into the VLOOKUP, rather than
>pulling the dynamic number into the function?
>
>Here's my VLOOKUP just for reference:
>=(VLOOKUP(F3,VLOOKUPrates!$B$2:$E$74,VLOOKUPrates!$B$2:$B$74))*M3
>where F3 is the calculated AGE and M3 is a constant.
>
>Here's my present AGE calculation for reference (that is somehow considered
>in the above VLOOKUP):
>=IF(TRUNC(((YEAR($A$1)-YEAR(E3))*12+MONTH($A$1)-MONTH(E3))/12)=105, "
>",TRUNC(((YEAR($A$1)-YEAR(E3))*12+MONTH($A$1)-MONTH(E3))/12))
>Where $A$1 = NOW
>
>any help gratefully acknowleged and appreciated !

A few suggestions.

1. To calculate age, use the simpler (but mostly undocumented in Excel)
formula:

=IF(OR(E3="",E3>TODAY()),"",DATEDIF(E3,TODAY(),"y"))

or, with A1: =TODAY()

=IF(OR(E3="",E3>A1),"",DATEDIF(E3,A1,"y"))

2. The syntax of your VLOOKUP is incorrect. The third argument is supposed to
be a column number within your table. You have:

=(VLOOKUP(F3,VLOOKUPrates!$B$2:$E$74,VLOOKUPrates!$B$2:$B$74))*M3

But perhaps:

VLOOKUP(F3,VLOOKUPRates!$B$2:$E$74,4)*M3


--ron