Is this approach correct ?

[Arts]

Hi Guys
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I have a two bit problem so shall naturally start with the first part
<o> </o>
I have been asked to highlight any new deals that may have occurred from a report that is produced weekly, so I have done the following formula
<o> </o>
=IF(ISNA(VLOOKUP(C15,'[TB Europe Pipeline summary July 06 2008.xls]DealDetails'!C$15:C$5619,1,0)),"New Deal",VLOOKUP(C15,'[TB Europe Pipeline summary July 06 2008.xls]DealDetails'!C$15:C$5619,1,0))
<o> </o>
basically compares this weeks report from last weeks and where no value is found this will read “New deal”
<o> </o>
What I was then asked was that the deals that already exist don’t need to be shown, these need to be shown as blank and only the New deals need to be shown so I then created the following formula
<o> </o>
=IF(ISTEXT(VLOOKUP(C24,'[TB Europe Pipeline summary July 06 2008.xls]DealDetails'!C$15:C$5619,1,0)),"","New Deal")
<o> </o>
would the above formula be correct in what I would need to do

Arts<o></o>

 

[Andrew Poulsom]

Couldn't you just AutoFilter for New Deal to hide the unwanted rows?

 

[Richard Schollar]

I would probably write it as:

=IF(ISNUMBER(MATCH(C24,'[TB Europe Pipeline summary July 06 2008.xls]DealDetails'!C$15:C$5619,0)),"","New Deal")

But there's nothing wrong with what you have done

 

[Arts]

Andrew

I did suggest this but the report needs to go out to various departments and all the deals need to be shown, they may how ever use the filter after wards but as it stands just new deals needed to be shown at a glance

Richard

Thank you for the confirmation. (I wont ask for an explanation asking why you would use your method over mine as long as mine works just happy to have come out with something that works, though feel free to if you wish)

My second part to the problem which i havent come up with a solution for is where "New Deal" has occured i would like the Deal id which is in column C to be highlighted in blue.

I was thinking conditional formatting was the way to go but that was as far as i got

Arts

 

[Richard Schollar]

Yep I would use conditional formatting - select column C and go Format>Conditional Formatting and change to Formula Is and use a formula like:

=$M1="New Deal"

and hit the Format button and format as required. Note I have assumed that the "New Deal" formula is being placed in column M - amend as appropriate.

 

[Arts]

Thanks Richard

That worked perfectly I was expecting a longer formula of some sort but that was quite straight forward.