| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
*******I Need Help******
Hello All,
I hope some one expert can help my problem...that is i have excel book for two sheets in sheet one some coloumns "NO"with back ground highlited Yellow colour.that is every 15days we have checked our vehicles if some documets are not we will mention NO.after 15days again we have to check same vehicles that day also if it is not the same documets automaticly it will come No with Red colour background????i want to know how it is possible...if you are not understand just give me your mail id i will send you the excel sheet.pls help me for my great future in my office. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
*******I Need Help******
Check out this link on Conditional formatting with dates
http://www.mrexcel.com/articles/due-...formatting.php -- Jacob (MVP - Excel) "Rohinikumar" wrote: Hello All, I hope some one expert can help my problem...that is i have excel book for two sheets in sheet one some coloumns "NO"with back ground highlited Yellow colour.that is every 15days we have checked our vehicles if some documets are not we will mention NO.after 15days again we have to check same vehicles that day also if it is not the same documets automaticly it will come No with Red colour background????i want to know how it is possible...if you are not understand just give me your mail id i will send you the excel sheet.pls help me for my great future in my office. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
*******I Need Help******
Hi which you have given that si not correct for my question....can you give
me your mail id i will send excel sheet. "Jacob Skaria" wrote: Check out this link on Conditional formatting with dates http://www.mrexcel.com/articles/due-...formatting.php -- Jacob (MVP - Excel) "Rohinikumar" wrote: Hello All, I hope some one expert can help my problem...that is i have excel book for two sheets in sheet one some coloumns "NO"with back ground highlited Yellow colour.that is every 15days we have checked our vehicles if some documets are not we will mention NO.after 15days again we have to check same vehicles that day also if it is not the same documets automaticly it will come No with Red colour background????i want to know how it is possible...if you are not understand just give me your mail id i will send you the excel sheet.pls help me for my great future in my office. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
*******I Need Help******
In the excel sheet I just highlighted colors manually but I want auto formula
like in the first sheet some vehicles doesnt have some documents then we mentioned No with yellow colour and Yes in green color, later 15 days again we have to audit the same vehicles then we compare both sheets that time if same documents have in the same vehicle then it will come in green color if not the document it will come to in red color. Is it possible? "Jacob Skaria" wrote: Check out this link on Conditional formatting with dates http://www.mrexcel.com/articles/due-...formatting.php -- Jacob (MVP - Excel) "Rohinikumar" wrote: Hello All, I hope some one expert can help my problem...that is i have excel book for two sheets in sheet one some coloumns "NO"with back ground highlited Yellow colour.that is every 15days we have checked our vehicles if some documets are not we will mention NO.after 15days again we have to check same vehicles that day also if it is not the same documets automaticly it will come No with Red colour background????i want to know how it is possible...if you are not understand just give me your mail id i will send you the excel sheet.pls help me for my great future in my office. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
*******I Need Help******
Why dont you post few sample data or explain how the data is arranged...
-- Jacob (MVP - Excel) "Rohinikumar" wrote: In the excel sheet I just highlighted colors manually but I want auto formula like in the first sheet some vehicles doesnt have some documents then we mentioned No with yellow colour and Yes in green color, later 15 days again we have to audit the same vehicles then we compare both sheets that time if same documents have in the same vehicle then it will come in green color if not the document it will come to in red color. Is it possible? "Jacob Skaria" wrote: Check out this link on Conditional formatting with dates http://www.mrexcel.com/articles/due-...formatting.php -- Jacob (MVP - Excel) "Rohinikumar" wrote: Hello All, I hope some one expert can help my problem...that is i have excel book for two sheets in sheet one some coloumns "NO"with back ground highlited Yellow colour.that is every 15days we have checked our vehicles if some documets are not we will mention NO.after 15days again we have to check same vehicles that day also if it is not the same documets automaticly it will come No with Red colour background????i want to know how it is possible...if you are not understand just give me your mail id i will send you the excel sheet.pls help me for my great future in my office. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
*******I Need Help******
Now you can understand what i am asking
I am using excel book with two sheets In the excel sheet1 monthly twice we have auditing our company vehicles some vehicles doesnt have some documents then we mentioned No with back ground yellow color and If YES then we mention in green color in the sheet1, later 15 days again we have to audit the same vehicles and we compare both sheet1 to sheet2 that time if same documents have YES in the same vehicle then it will come in green color if not the document in same vehicle and same document it will come in NO red color back ground. I dont idea how to compare and how to put formula. Is it possible? Sheet 1 Reg No. Insurance Road Tax Vehicle TYESpe Model Speed GoverNOr Seat Belt TN22 BZ 7101 YES NO BUS 2008 NO NO TN22 BZ 8211 YES NO BUS 2008 NO NO KA01 B 8639 YES YES BUS 2006 NO NO TN22 BX1712 YES YES Mini BUS 2008 NO NO KA01 B 8056 YES YES BUS 2006 NO NO TN22 AW 4776 YES NO BUS 2008 NO NO Sheet 2 Reg No. Insurance Road Tax Vehicle TYESpe Model Speed GoverNOr Seat Belt TN22 BZ 7101 YES NO BUS 2008 NO NO TN22 BZ 8211 YES NO BUS 2008 NO Yes KA01 B 8639 YES YES BUS 2006 YES Yes TN22 BX1712 YES YES Mini BUS 2008 NO NO KA01 B 8056 YES YES BUS 2006 NO NO TN22 AW 4776 YES NO BUS 2008 NO NO "Jacob Skaria" wrote: Why dont you post few sample data or explain how the data is arranged... -- Jacob (MVP - Excel) "Rohinikumar" wrote: In the excel sheet I just highlighted colors manually but I want auto formula like in the first sheet some vehicles doesnt have some documents then we mentioned No with yellow colour and Yes in green color, later 15 days again we have to audit the same vehicles then we compare both sheets that time if same documents have in the same vehicle then it will come in green color if not the document it will come to in red color. Is it possible? "Jacob Skaria" wrote: Check out this link on Conditional formatting with dates http://www.mrexcel.com/articles/due-...formatting.php -- Jacob (MVP - Excel) "Rohinikumar" wrote: Hello All, I hope some one expert can help my problem...that is i have excel book for two sheets in sheet one some coloumns "NO"with back ground highlited Yellow colour.that is every 15days we have checked our vehicles if some documets are not we will mention NO.after 15days again we have to check same vehicles that day also if it is not the same documets automaticly it will come No with Red colour background????i want to know how it is possible...if you are not understand just give me your mail id i will send you the excel sheet.pls help me for my great future in my office. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
*******I Need Help******
Hi Rohinikumar
Select sheet1 data and name that range as say "myName" . From menu InsertNameDefine 1. In Sheet2 select the data range say B1:G1000. Make sure the active cell is B1. Active cell will have a white background even after selection 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(VLOOKUP($A1,myName,COLUMN(),0)="NO",B1="NO") 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK PS: If you are using XL2007 Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format. Enter the formula in the box below. -- Jacob (MVP - Excel) "Rohinikumar" wrote: Now you can understand what i am asking I am using excel book with two sheets In the excel sheet1 monthly twice we have auditing our company vehicles some vehicles doesnt have some documents then we mentioned No with back ground yellow color and If YES then we mention in green color in the sheet1, later 15 days again we have to audit the same vehicles and we compare both sheet1 to sheet2 that time if same documents have YES in the same vehicle then it will come in green color if not the document in same vehicle and same document it will come in NO red color back ground. I dont idea how to compare and how to put formula. Is it possible? Sheet 1 Reg No. Insurance Road Tax Vehicle TYESpe Model Speed GoverNOr Seat Belt TN22 BZ 7101 YES NO BUS 2008 NO NO TN22 BZ 8211 YES NO BUS 2008 NO NO KA01 B 8639 YES YES BUS 2006 NO NO TN22 BX1712 YES YES Mini BUS 2008 NO NO KA01 B 8056 YES YES BUS 2006 NO NO TN22 AW 4776 YES NO BUS 2008 NO NO Sheet 2 Reg No. Insurance Road Tax Vehicle TYESpe Model Speed GoverNOr Seat Belt TN22 BZ 7101 YES NO BUS 2008 NO NO TN22 BZ 8211 YES NO BUS 2008 NO Yes KA01 B 8639 YES YES BUS 2006 YES Yes TN22 BX1712 YES YES Mini BUS 2008 NO NO KA01 B 8056 YES YES BUS 2006 NO NO TN22 AW 4776 YES NO BUS 2008 NO NO "Jacob Skaria" wrote: Why dont you post few sample data or explain how the data is arranged... -- Jacob (MVP - Excel) "Rohinikumar" wrote: In the excel sheet I just highlighted colors manually but I want auto formula like in the first sheet some vehicles doesnt have some documents then we mentioned No with yellow colour and Yes in green color, later 15 days again we have to audit the same vehicles then we compare both sheets that time if same documents have in the same vehicle then it will come in green color if not the document it will come to in red color. Is it possible? "Jacob Skaria" wrote: Check out this link on Conditional formatting with dates http://www.mrexcel.com/articles/due-...formatting.php -- Jacob (MVP - Excel) "Rohinikumar" wrote: Hello All, I hope some one expert can help my problem...that is i have excel book for two sheets in sheet one some coloumns "NO"with back ground highlited Yellow colour.that is every 15days we have checked our vehicles if some documets are not we will mention NO.after 15days again we have to check same vehicles that day also if it is not the same documets automaticly it will come No with Red colour background????i want to know how it is possible...if you are not understand just give me your mail id i will send you the excel sheet.pls help me for my great future in my office. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
*******I Need Help******
Hi Jacob thanks for your help but some little confustion,could you give me
your Email ID i will send my excel sheet then you can put format and send it back to me..pls "Jacob Skaria" wrote: Hi Rohinikumar Select sheet1 data and name that range as say "myName" . From menu InsertNameDefine 1. In Sheet2 select the data range say B1:G1000. Make sure the active cell is B1. Active cell will have a white background even after selection 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(VLOOKUP($A1,myName,COLUMN(),0)="NO",B1="NO") 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK PS: If you are using XL2007 Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format. Enter the formula in the box below. -- Jacob (MVP - Excel) "Rohinikumar" wrote: Now you can understand what i am asking I am using excel book with two sheets In the excel sheet1 monthly twice we have auditing our company vehicles some vehicles doesnt have some documents then we mentioned No with back ground yellow color and If YES then we mention in green color in the sheet1, later 15 days again we have to audit the same vehicles and we compare both sheet1 to sheet2 that time if same documents have YES in the same vehicle then it will come in green color if not the document in same vehicle and same document it will come in NO red color back ground. I dont idea how to compare and how to put formula. Is it possible? Sheet 1 Reg No. Insurance Road Tax Vehicle TYESpe Model Speed GoverNOr Seat Belt TN22 BZ 7101 YES NO BUS 2008 NO NO TN22 BZ 8211 YES NO BUS 2008 NO NO KA01 B 8639 YES YES BUS 2006 NO NO TN22 BX1712 YES YES Mini BUS 2008 NO NO KA01 B 8056 YES YES BUS 2006 NO NO TN22 AW 4776 YES NO BUS 2008 NO NO Sheet 2 Reg No. Insurance Road Tax Vehicle TYESpe Model Speed GoverNOr Seat Belt TN22 BZ 7101 YES NO BUS 2008 NO NO TN22 BZ 8211 YES NO BUS 2008 NO Yes KA01 B 8639 YES YES BUS 2006 YES Yes TN22 BX1712 YES YES Mini BUS 2008 NO NO KA01 B 8056 YES YES BUS 2006 NO NO TN22 AW 4776 YES NO BUS 2008 NO NO "Jacob Skaria" wrote: Why dont you post few sample data or explain how the data is arranged... -- Jacob (MVP - Excel) "Rohinikumar" wrote: In the excel sheet I just highlighted colors manually but I want auto formula like in the first sheet some vehicles doesnt have some documents then we mentioned No with yellow colour and Yes in green color, later 15 days again we have to audit the same vehicles then we compare both sheets that time if same documents have in the same vehicle then it will come in green color if not the document it will come to in red color. Is it possible? "Jacob Skaria" wrote: Check out this link on Conditional formatting with dates http://www.mrexcel.com/articles/due-...formatting.php -- Jacob (MVP - Excel) "Rohinikumar" wrote: Hello All, I hope some one expert can help my problem...that is i have excel book for two sheets in sheet one some coloumns "NO"with back ground highlited Yellow colour.that is every 15days we have checked our vehicles if some documets are not we will mention NO.after 15days again we have to check same vehicles that day also if it is not the same documets automaticly it will come No with Red colour background????i want to know how it is possible...if you are not understand just give me your mail id i will send you the excel sheet.pls help me for my great future in my office. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
