Hi All,

I have two Dynamic Ranges: "ACTUAL" spans 7 Columns and many Rows. "FORECAST"
is one Column and many Rows.

I would like to check if the numeric values in the LAST Column of "ACTUAL"
match the values in "FORECAST" on the same Row. If there is a match on the
same Row, I would like a text string of "yes" returned; blank if no match.

The numeric values in "ACTUAL" are all integers. However, the numeric values
in "FORECAST" are a mix of integers and numbers to five decimal places.

So, if the LAST Column of "ACTUAL" has a value of 61 and the corresponding
Row in "FORECAST" has a value of 61.33333 then, this should be taken as a
match. However, if the value in "FORECAST" is 61.66667 then, this is viewed
as 62 and is NOT a match with 61.

Thanks
Sam


--
Message posted via http://www.officekb.com

Hi!

From your description it sounds like only the number of rows in your ranges
are dynamic.

Just because the ranges are named doesn't mean you have to use those names
in any formulas.

Assume that column 7 of ACTUAL is in column G, G2:Gn

FORECAST is in column H, H2:Hn

=IF(OR(G2="",H2=""),"",IF(ROUND(H2,0)=G2,"Yes","") )

Copy down to enough rows to allow for future addition of data in the named
ranges.

Now, here's that same formula using the range names:

=IF(OR(INDEX(Actual,ROW(1:1),7)="",INDEX(Forecast, ROW(1:1))=""),"",IF(ROUND(INDEX(Forecast,ROW(1:1)) ,0)=INDEX(Actual,ROW(1:1),7),"Yes",""))

Biff

"Sam via OfficeKB.com" wrote in message
...
Hi All,

I have two Dynamic Ranges: "ACTUAL" spans 7 Columns and many Rows.
"FORECAST"
is one Column and many Rows.

I would like to check if the numeric values in the LAST Column of
"ACTUAL"
match the values in "FORECAST" on the same Row. If there is a match on the
same Row, I would like a text string of "yes" returned; blank if no match.

The numeric values in "ACTUAL" are all integers. However, the numeric
values
in "FORECAST" are a mix of integers and numbers to five decimal places.

So, if the LAST Column of "ACTUAL" has a value of 61 and the
corresponding
Row in "FORECAST" has a value of 61.33333 then, this should be taken as a
match. However, if the value in "FORECAST" is 61.66667 then, this is
viewed
as 62 and is NOT a match with 61.

Thanks
Sam


--
Message posted via http://www.officekb.com

Hi Biff,

Thank you very much for both alternatives - great! Very much appreciated.

Biff wrote:
Assume that column 7 of ACTUAL is in column G, G2:Gn
FORECAST is in column H, H2:Hn
=IF(OR(G2="",H2=""),"",IF(ROUND(H2,0)=G2,"Yes","" ))

Copy down to enough rows to allow for future addition of data in the named ranges.

Now, here's that same formula using the range names:
=IF(OR(INDEX(Actual,ROW(1:1),7)="",INDEX(Forecast ,ROW(1:1))=""),"",IF(ROUND(INDEX(Forecast,ROW(1:1) ),0)=INDEX(Actual,ROW(1:1),7),"Yes",""))

Cheers,
Sam


--
Message posted via http://www.officekb.com

You're welcome. Thanks for the feedback!

Biff

"Sam via OfficeKB.com" wrote in message
...
Hi Biff,

Thank you very much for both alternatives - great! Very much appreciated.

Biff wrote:
Assume that column 7 of ACTUAL is in column G, G2:Gn
FORECAST is in column H, H2:Hn
=IF(OR(G2="",H2=""),"",IF(ROUND(H2,0)=G2,"Yes"," "))

Copy down to enough rows to allow for future addition of data in the named
ranges.

Now, here's that same formula using the range names:
=IF(OR(INDEX(Actual,ROW(1:1),7)="",INDEX(Forecas t,ROW(1:1))=""),"",IF(ROUND(INDEX(Forecast,ROW(1:1 )),0)=INDEX(Actual,ROW(1:1),7),"Yes",""))

Cheers,
Sam


--
Message posted via http://www.officekb.com