Can anyone help me with the following I am getting a value error from this
statement
=IF(A18:A32=D3),"eff","not eff"
On worksheet €˜Sheet2 there is a data table in range A17 €“ J32 showing a
rate table and starting effective dates from top to bottom in column A. The
rate in each row is effective for the date found on the left and effective
until a new row and date are entered below that row. Example: The rates in
row 20 are effective from 5/1/2004 until 10/1/2004.

In cell E7 write a formula that will display the phrase, €œEffective Date€� in
the cell if the date entered in cell D3 is a date a rate became effective.
If a match to the date cannot be found in column A, the formula should
display €œNot an Effective Date€�.
Thanks

Hi Dianne:

Do you want True to mean ANY of A18 thru A32 matching D3, or do you mean
that ALL of D18 thru A32 matches D3??
--
Gary''s Student - gsnu200908


"Dianne" wrote:

Can anyone help me with the following I am getting a value error from this
statement
=IF(A18:A32=D3),"eff","not eff"
On worksheet €˜Sheet2 there is a data table in range A17 €“ J32 showing a
rate table and starting effective dates from top to bottom in column A. The
rate in each row is effective for the date found on the left and effective
until a new row and date are entered below that row. Example: The rates in
row 20 are effective from 5/1/2004 until 10/1/2004.

In cell E7 write a formula that will display the phrase, €œEffective Date€� in
the cell if the date entered in cell D3 is a date a rate became effective.
If a match to the date cannot be found in column A, the formula should
display €œNot an Effective Date€�.
Thanks

"Dianne" wrote:

Can anyone help me with the following I am getting a value error from this
statement
=IF(A18:A32=D3),"eff","not eff"
On worksheet €˜Sheet2 there is a data table in range A17 €“ J32 showing a
rate table and starting effective dates from top to bottom in column A. The
rate in each row is effective for the date found on the left and effective
until a new row and date are entered below that row. Example: The rates in
row 20 are effective from 5/1/2004 until 10/1/2004.

In cell E7 write a formula that will display the phrase, €œEffective Date€� in
the cell if the date entered in cell D3 is a date a rate became effective.
If a match to the date cannot be found in column A, the formula should
display €œNot an Effective Date€�.
Thanks

Hello Dianne
change the formula to read =IF(A18:A32=D3,"eff","not eff") for your first
question.

I nee to have it be true if any of A18 thru A32 are a match.

Thanks,
Dianne
"Gary''s Student" wrote:

Hi Dianne:

Do you want True to mean ANY of A18 thru A32 matching D3, or do you mean
that ALL of D18 thru A32 matches D3??
--
Gary''s Student - gsnu200908


"Dianne" wrote:

Can anyone help me with the following I am getting a value error from this
statement
=IF(A18:A32=D3),"eff","not eff"
On worksheet €˜Sheet2 there is a data table in range A17 €“ J32 showing a
rate table and starting effective dates from top to bottom in column A. The
rate in each row is effective for the date found on the left and effective
until a new row and date are entered below that row. Example: The rates in
row 20 are effective from 5/1/2004 until 10/1/2004.

In cell E7 write a formula that will display the phrase, €œEffective Date€� in
the cell if the date entered in cell D3 is a date a rate became effective.
If a match to the date cannot be found in column A, the formula should
display €œNot an Effective Date€�.
Thanks

Hi,

I made the change but I am still getting a value error when the formula runs.

Thanks,
Dianne

"Buschwack" wrote:



"Dianne" wrote:

Can anyone help me with the following I am getting a value error from this
statement
=IF(A18:A32=D3),"eff","not eff"
On worksheet €˜Sheet2 there is a data table in range A17 €“ J32 showing a
rate table and starting effective dates from top to bottom in column A. The
rate in each row is effective for the date found on the left and effective
until a new row and date are entered below that row. Example: The rates in
row 20 are effective from 5/1/2004 until 10/1/2004.

In cell E7 write a formula that will display the phrase, €œEffective Date€� in
the cell if the date entered in cell D3 is a date a rate became effective.
If a match to the date cannot be found in column A, the formula should
display €œNot an Effective Date€�.
Thanks

Hello Dianne
change the formula to read =IF(A18:A32=D3,"eff","not eff") for your first
question.

Dianne -

This will look up the value in cell D3 to see if it exists in the range
A18:A32, and if it isn't there, will display "not eff", if it is there, it
will display "eff":

=IF(isna(vlookup(D3,A18:A32,1,FALSE)),"not eff","eff")

--
Daryl S


"Dianne" wrote:

Can anyone help me with the following I am getting a value error from this
statement
=IF(A18:A32=D3),"eff","not eff"
On worksheet €˜Sheet2 there is a data table in range A17 €“ J32 showing a
rate table and starting effective dates from top to bottom in column A. The
rate in each row is effective for the date found on the left and effective
until a new row and date are entered below that row. Example: The rates in
row 20 are effective from 5/1/2004 until 10/1/2004.

In cell E7 write a formula that will display the phrase, €œEffective Date€� in
the cell if the date entered in cell D3 is a date a rate became effective.
If a match to the date cannot be found in column A, the formula should
display €œNot an Effective Date€�.
Thanks

Daryl,

Thanks, this works kind of. I need it to do this for each cell from A18 to
A32 and as it compares each one write the eff or not eff to the cell that it
is comparing to...sorry I was not being very clear.

Thanks,
Dianne

"Daryl S" wrote:

Dianne -

This will look up the value in cell D3 to see if it exists in the range
A18:A32, and if it isn't there, will display "not eff", if it is there, it
will display "eff":

=IF(isna(vlookup(D3,A18:A32,1,FALSE)),"not eff","eff")

--
Daryl S


"Dianne" wrote:

Can anyone help me with the following I am getting a value error from this
statement
=IF(A18:A32=D3),"eff","not eff"
On worksheet €˜Sheet2 there is a data table in range A17 €“ J32 showing a
rate table and starting effective dates from top to bottom in column A. The
rate in each row is effective for the date found on the left and effective
until a new row and date are entered below that row. Example: The rates in
row 20 are effective from 5/1/2004 until 10/1/2004.

In cell E7 write a formula that will display the phrase, €œEffective Date€� in
the cell if the date entered in cell D3 is a date a rate became effective.
If a match to the date cannot be found in column A, the formula should
display €œNot an Effective Date€�.
Thanks