Is this approach correct ?

Arts

Well-known Member
Joined
Sep 28, 2007
Messages
514
Office Version
  1. 2016
Platform
  1. Windows
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>
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

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

Well-known Member
Joined
Sep 28, 2007
Messages
514
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Sep 28, 2007
Messages
514
Office Version
  1. 2016
Platform
  1. Windows
Thanks Richard

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,990
Messages
5,834,743
Members
430,315
Latest member
bobh63

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