Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-05-2012, 12:54 PM
MikeJedi MikeJedi is offline Need formula for search and conditional formatting Windows XP Need formula for search and conditional formatting Office 2003
Novice
Need formula for search and conditional formatting
 
Join Date: Dec 2009
Posts: 6
MikeJedi is on a distinguished road
Default Need formula for search and conditional formatting

Hey folks!



I'm in need of some assistance for a project at work. We're trying to automate a report by conditionally formatting a cell or cells based on information that is updated daily on a separate worksheet in the workbook. Normally this wouldn't be a problem, but I need to match data in 2 columns, then grab the value of a third column in that matching row. Based on the value in that 3rd column, I want to color a cell in another sheet.

So, find a match in Col C, of which there will be many. Then in those rows that qualify, find a specific value in Col D. Once that row is identified (there can be only one row that matches both), go look at Col J and determine if it contains a 1 or 0. If it is a 1, apply a color to the cell on Sheet 2. Each cell in 'Sheet 2'!C3:AA4 (or more) should run this test on Sheet 1 to determine if it's cell should be colored or not.

I've attached a sample so you can see what I'm talking about.

Can anyone help with a formula that will make this happen?

There will be thousands of rows on Sheet 1 and it will be updated (pasted over) daily, so the formula should account for that size.

Thanks in advance!
Attached Files
File Type: xls Sample.xls (145.5 KB, 14 views)
Reply With Quote
  #2  
Old 01-05-2012, 06:54 PM
macropod's Avatar
macropod macropod is offline Need formula for search and conditional formatting Windows 7 64bit Need formula for search and conditional formatting Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Mike,

If you format your Sheet 2 cells as General, instead of Text, you could use:
=SUMPRODUCT(('Sheet 1'!$C$7:$C$15=$A$3)*('Sheet 1'!$B$7:$B$15=$B$3),'Sheet 1'!J$7:J$15)
This will return 0 if there are no matching 1s in column J, or a number greater than 0 otherwise (unless, perhaps, you have -ve numbers in column J).
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 01-06-2012, 01:03 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Need formula for search and conditional formatting Windows XP Need formula for search and conditional formatting Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Does the attached help?
Attached Files
File Type: xlsx Copy of sample.xlsx (56.7 KB, 12 views)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #4  
Old 01-06-2012, 02:22 PM
MikeJedi MikeJedi is offline Need formula for search and conditional formatting Windows XP Need formula for search and conditional formatting Office 2003
Novice
Need formula for search and conditional formatting
 
Join Date: Dec 2009
Posts: 6
MikeJedi is on a distinguished road
Default

Thank you for the quick replies. I haven't had a chance to look at your suggestions yet because it's been very hectic today, but I will try them out and get back with you both.

Thanks again!
Reply With Quote
  #5  
Old 01-11-2012, 02:18 PM
MikeJedi MikeJedi is offline Need formula for search and conditional formatting Windows XP Need formula for search and conditional formatting Office 2003
Novice
Need formula for search and conditional formatting
 
Join Date: Dec 2009
Posts: 6
MikeJedi is on a distinguished road
Default

macropod,

I was not able to test your formula successfully, mostly because I am not sure where to put it and how to make the cells turn green when the value is >0.

Pecoflyer,

Your modifications to the sample worked flawlessly. However, I could not figure out how to incorporate the changes into the working copy. Due to the sensitive nature of the data, I cannot post the actual file here, so can you walk me through the process to make this work?

Thanks to you both!
Reply With Quote
  #6  
Old 01-12-2012, 06:01 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Need formula for search and conditional formatting Windows XP Need formula for search and conditional formatting Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

OK Mike, first things first - see attached for the named ranges ( CF will not work on different sheets any other way)
So you will have to do the same on your data ( names can be anything just avoid XL terms like sheet, workbook, column,etc..)
Attached Files
File Type: xlsx Copy of Copy of sample.xlsx (57.6 KB, 12 views)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #7  
Old 01-13-2012, 08:57 AM
MikeJedi MikeJedi is offline Need formula for search and conditional formatting Windows XP Need formula for search and conditional formatting Office 2003
Novice
Need formula for search and conditional formatting
 
Join Date: Dec 2009
Posts: 6
MikeJedi is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
OK Mike, first things first - see attached for the named ranges ( CF will not work on different sheets any other way)
So you will have to do the same on your data ( names can be anything just avoid XL terms like sheet, workbook, column,etc..)
Ok, I have named my ranges on the first sheet (used the same that you did).

Now what?
Reply With Quote
  #8  
Old 01-13-2012, 10:20 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Need formula for search and conditional formatting Windows XP Need formula for search and conditional formatting Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Open sheet 2 - Select the range to format ( say C3:AA4)
Select the Home Tab - Cond Formatting - manage rules
Click New rule - Use a formula....
Select " New rule and enter
Code:
=SUMPRODUCT(--(zip1=$B4),--(route=TEXT(C4,"000"))*quest1)=1
Select which format you want and you're done

If you are not familiar with the SUMPRODUCT function read this link


HTH
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #9  
Old 01-13-2012, 11:15 AM
MikeJedi MikeJedi is offline Need formula for search and conditional formatting Windows XP Need formula for search and conditional formatting Office 2010 32bit
Novice
Need formula for search and conditional formatting
 
Join Date: Dec 2009
Posts: 6
MikeJedi is on a distinguished road
Default

Thank you! I had to modify a couple of things, but the formatting is now working perfectly. My biggest mistake was in my named ranges. Since I wanted to allow for future growth of the ranges, I just selected a random range somewhere around cell 5000, but all three ranges were different.

After reading through the link in your previous post I found out that the ranges have to be the same size when used in a calculation. When I changed them all to 5000, the formatting materialized!

Thanks again!
Reply With Quote
  #10  
Old 01-13-2012, 12:38 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Need formula for search and conditional formatting Windows XP Need formula for search and conditional formatting Office 2003
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

My mistake, I forgot to mention that
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need formula for search and conditional formatting conditional formatting formula NEHicks503 Excel 1 11-30-2011 09:01 AM
Need formula for search and conditional formatting IF and Conditional Formatting - Ver.2003 zeppelinmike Excel 1 11-13-2011 08:05 AM
Need formula for search and conditional formatting Conditional formatting with AND, OR Lucky Excel 2 10-03-2011 11:41 PM
Conditional Formatting namedujour Excel 3 08-25-2011 01:46 PM
Need formula for search and conditional formatting help with conditional formatting Snvlsfoal Excel 3 07-03-2011 11:55 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:05 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft