Is this approach correct ?

Arts

Active Member
Joined
Sep 28, 2007
Messages
495
Hi Guys
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
I have a two bit problem so shall naturally start with the first part
<o:p> </o:p>
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:p> </o:p>
=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:p> </o:p>
basically compares this weeks report from last weeks and where no value is found this will read “New deal”
<o:p> </o:p>
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:p> </o:p>
=IF(ISTEXT(VLOOKUP(C24,'[TB Europe Pipeline summary July 06 2008.xls]DealDetails'!C$15:C$5619,1,0)),"","New Deal")
<o:p> </o:p>
would the above formula be correct in what I would need to do

Arts<o:p></o:p>
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Active Member
Joined
Sep 28, 2007
Messages
495
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Active Member
Joined
Sep 28, 2007
Messages
495
Thanks Richard

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

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top