help with Conditional formatting pairs of cells

I have a spreadsheet which is laid out as a burial ground map showing who is 
buried where and whether a headstone is in place.

2 cells represent one grave as there may be 2 people buried there.

The format has 4 conditions:

Empty
used
used+memorial
reserved.

The sheet has 10 columns of around 20 pairs of cells.

Conditional format over the whole range works fine when the pair of cells 
for a grave are marked empty or where both cells are used (for 2 people in a 
grave).

My problem is finding an easy way to conditionally format the second cell of 
each pair to match the first when the grave has only one occupant.

Presently where only one person is buried (shown in the first cell) the 
second cell formats as empty as the rule dictates.

The columns are each separated by one blank column, all the cells in a 
column are adjacent.

Any help welcome

regards

Pete

  
0
Utf
1/28/2010 3:41:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
566 Views

Similar Articles

[PageSpeed] 48

Have you tried an if statement?

I assume you are filling the burial ground map with names from master list 
with vlookup and the conditional formatting trigger is text within the cell.

Can you include an if statement on the second cell of the grave that if 
there is only 1 name to the grave to say “half” or similar, which would 
trigger the used conditional formatting?

Ando

0
Utf
1/29/2010 4:16:01 AM
Reply:

Similar Artilces:

Excel 2007 3 Traiffic Light Conditional Formatting
Does anyone know how to get this to work using the 3-light conditional formatting in Excel 2007 using the reference to another cell? I need it pretty much the same way as listed above, but it is not working for me. Here is the set up I currently have. ROW 1 MTD ACTUAL BUDGET DIFFERENCE ROW 2 120 120 130 -10 I want the 3-Light Conditional Formatting to be used on the MTD column with this formula set up below. GREEN LIGHT >= =D2=0 YELLOW LIGHT >= =D2=(-3) RED LIGHT Why is this not working? Any and every bit of help would be most appreciated! Thanks, nathan Nathan, AFAIK, you ca...

help! Can't open money file
"money has detected you did not close your money file before exiting. Money now needs to verify the information in your file" When I click yes I get: "the email address or password is incorrect. Please try again." any suggestions? sorry forgot to mention: Money 2005 Windows XP "Jody Miller" <jodyemberly@hotmail.com> wrote in message news:wHc_e.75951$Jp.2009910@twister.southeast.rr.com... > "money has detected you did not close your money file before exiting. > Money now needs to verify the information in your file" > > Wh...

HELP with adding email accounts
I have Outlook 2000 and I am trying to add email accounts for my various businesses. I can add one and the other two give me the error message 0x800ccc0d. I run Windows XP and I am on DSL. Can anyone advise? Hi Wendy, WORKAROUND To work around this problem, use one of the following methods, depending on your situation. Method 1: Confirm that your e-mail server settings are correct For information about the correct settings for your e-mail server, contact your Internet service provider (ISP) or system administrator. Note Some ISPs do not permit message routing through another SMTP ser...

Change date format on report
My date displays as mddyy in the table but on the report I'd like it to display as mm/dd/yyyy, how would I code that to have that field always display in mm/dd/yyyy format. I'm thinking this is very simple but I just can't get it to work for me. Thank so much for your help. On May 2, 1:50 pm, SITCFanTN <SITCFa...@discussions.microsoft.com> wrote: > My date displays as mddyy in the table but on the report I'd like it to > display as mm/dd/yyyy, how would I code that to have that field always > display in mm/dd/yyyy format. I'm thinking this is very simple...

Broken links don't show old data. Help!
I have a spreadsheet in an email attachment that no longer shows old data from broken links after upgrading to Office XP. The data is there, and displays while the "Update links" dialog box is up, but no matter what I choose the data then disappears into a sea of #REF when I dismiss the dialog box. How can I get the old data to show again? Thanks. -Mark ...

"Help Menu" does not close
How do I close the "Help Menu"? I notice on both Entourage Help and Word Help, that there is no close or hide button. The Help Menu just rides on top of the program until I quit the entire program. I can make the window really small by dragging the corner, but it still sits on top of the main program. I know I have to be missing something simple here, but I have been looking at this for 3 days and do not know how to get rid of it. Thank you, Michael Tall Entourage and Word Help do have a close and maximize button (the minimize button is greyed out). You probably don't noti...

Forms: can a cell itself be clickable?
Hi! I'm trying to create a form for my boss. I'm using the forms toolbar, but what it does is throw in a check box for instance, but if I change the cell in any way, the "picture" of the click box stays where it's at. Any way to change this? In other words, I don't want to the click box to be a picture, I want it to be an integral part of the cell, so I can modify it as needed (ie. place it centrered horizontally or vertically etc. Am I dreaming in colour? Thanks! Carmen I've seen a macro that will insert a checkmark when you select the cell, but I&#...

Can someone PLEASE Help me. I'm begging you.
Can someone....PLEASE HELP ME!! I run a small business. I have been using Outlook 2002 since I bought this computer in 2002. Everything had been going fine until a few weeks ago I started using Outlook Express. Big mistake!! Since then, I am unable to send/rececive messages through Outlook 2002, but Outlook Express works fine. Outlook Express and Outlook full version are integrated someway. I can't do anything with emails on the full version of Outlook now. It seems as if Outlook Express has taken over. Even when I try to send an email and use a particular account in Outlo...

Pictures in Excel cells
Dear friends I want to create a book with personal descriptions, including pictures. But how can I get a picture in an Excel cell? I thought that =HYPERLINK("file.jpg") would be the thing, but the pictures are not retrieved when I combine the Excel sheet to the final Word file. Please help. -- Jos� Do you object if ... Alt > i > p > f and select the picture what you want to import. HTH -- MRT "Jose" <jose@127.0.0.1> wrote in message news:e4nsg59mm09cuqgr2etqtm06jm87idk343@4ax.com... > Dear friends > > I want to create...

more formula help
Once again working on the fundraiser spreadsheet... Teacher wants ... of total money raised, 40% is profit...of that profit the first $15 goes to the general account, the difference goes in the student account. The only thing I really need to keep track of is the student account, which I figured would be (cell*.4)-15, but the problem I have is ... If the child raised zero dollars, it's showing a -$15 dollar balance, but I would need it to show zero. Any help is appreciated. Vivian K =MAX(yourexpression,0) -- Gary''s Student "Viviank" wrote: > Once again wo...

Max Date + condition
Hi all! In column 1, I have dates. In column 2, I have criterias. How can I ge the max date matching criteria in column 2. Example: Col 1 2004-08-01 2004-08-01 2004-08-02 2004-08-01 2004-08-05 Col 2 AA ABC AA CC CC How to get max date of AA ? I should get 2004-08-02 Thank you -- Message posted from http://www.ExcelForum.com Hi try the array formula (entered with CTRL+sHIFT+ENTER): =MAX(IF(B1:B100="AA",A1:A100)) >-----Original Message----- >Hi all! > >In column 1, I have dates. In column 2, I have criterias. How can I get >the max date matching criteria in co...

How to lock cell
Hi, How to lock a particular cell without locking the whole work sheet Pls help me. -- Moideen Moideen;1599749 Wrote: > Hi, > > How to lock a particular cell without locking the whole work sheet > > Pls help me. Do you mean have one particular cell that cannot be updated/altered but the rest can? Or do you mean have a locked cell but without protecting the work sheet? -- Spencer101 By default all cells on a sheet are locked when the sheet is protected. Select all cells(CTRL + a...........twice in Excel 2003) then go to format>cells>protection and unlo...

Header and cell referencing
My issue is this: I have 6 sheets. The first sheet contains the clients information. I want to take the last name of the client from the first sheet and have it appear in the header of every sheet after it. How do I create a cell reference inside a header? David David Sub Path_All_Sheets() Set wkbktodo = ActiveWorkbook For Each ws In wkbktodo.Worksheets ws.PageSetup.RightHeader = Sheets("Clients").Range("A1").Text Next End Sub Assumes client's name is in A1 Note: you can do the same thing by right-click on first sheet tab and "select all ...

cell content into a note
hey guys, just curious if this can be achieved or how it can be achieved say i have this Column A Row 1 = Short Description Column B Row 1 = Long descriptoin B2:B10 = are all the short descriptions B2:B10 = are all the long descriptions is there a way to just put the comment into the short description cells from the long description cells via a note and then hide the long description column? can anyone advise as to how this can be achieved? cheers Don't multipost. You could add Comments to the column A cells with the text from Column B cells and have Column B hidden. Sub Comment...

Cell Number Format
Hi, when i type for example this any number with more than 15 digits, the first 15 digits appear like i wrote it but the rest of them are changed by zeros. EX: i write this 123456789123456789 and the number that is kept is 123456789123456000 Does anyone experienced this and knows how to resolve it. Thanks, Ricardo As you can verify in XL Help ("Specifications"), XL is limited to 15 decimal digits of precision for numbers. If you're entering, say, a credit card number, which doesn't require any math to be performed on it, you can preformat the cell as Text or prefix ...

Move to match cells
My spreadsheet looks like this: A B C D E 1 000801025-8 009601025-3 2007 000801025-8 2007 2 000801026-9 009601026-4 2004 000801026-9 2004 3 000801028-1 009601028-6 2006 000801028-1 2006 4 000801036-8 009601036-3 2008 000801036-8 2008 5 000801044-5 009601044-0 2007 000801044-5 2007 6 000801044-5 009601055-0 2004 000801055-5 2004 7 000801055-5 009601064-8 2001 000801064-3 2001 8 000801055-5 009601091-2 2006 000801091-7 2006 9 000801064-3 009601095-6 2002 000801095-1 2002 10 000801091-7 009601097-8 1998 00...

Phone Format
One of my clients is using the letter writting assistant in GP 8, the problem is that when he tries to use LWA(Letter Writting Assistant), having Phone number field is what appears to be in US format, which is really annoying, i was wondering if anyone might have come accross a similar issue. Having said that I am unable to sell modifier to him because of his budget contraints and makes it really useless from his perspective to spend about 2000 odd quid over something this small. Any ideas on how to go about it without using Modifier. Make sure you have v8.0 Service Pack 4 installed...

Nonadjacent Cell Text Overflow
Is there a way to merge nonadjacent cells for text overflow. What I' trying to do is have text entered into a row of merged cells(e.g. Row A1:F1) but if the text is more than the cells can contain to overflo into another nonadjacent row of merged cells(Row3 A1:F1). Thanks fo any help with this -- Db171 ----------------------------------------------------------------------- Db1712's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1591 View this thread: http://www.excelforum.com/showthread.php?threadid=27498 ...

Table formatting lost
I receive emails that started off in Excel as a short table. The sender then cuts and pastes into Outlook. When the message arrives it has lost all formatting for the table. Is there any solution apart from sennding as an Excel attachment? Do the copy as a Picture and then paste in Outlook. -- Gary''s Student - gsnu2007k "him@privacy.net" wrote: > I receive emails that started off in Excel as a short table. The > sender then cuts and pastes into Outlook. When the message arrives it > has lost all formatting for the table. Is there any solution apart > from se...

Help! Appointments all moved by an hour!
Hi Everyone... I have Exchange Server 2003 on an Windows Server 2003 box. Latest updates for everything, but the time change moved all of my appointments on some calendars by 1 hour! Not all calendars were effected. I see there is a knowledgebase article on it, but it applies to the Jerusalem time zone, http://support.microsoft.com/kb/867475/, not sure if I can use it or not... anyone else experience this problem in the Eastern United States, Daylight Savings Time... anyone else had the same issue? Thanks for any help? Yes, I am having the same issue with the president of the comp...

Formatting Phone Numbers in a Merged Cells...
If I want to merge a "Company Name", "Phone Number" and a "Fax" number into 1 cell (for use in a drop down box), is there a way to maintain the 10 digit format for the phone/fax numbers? The phone/fax #'s appear as a string of 10 digits when they are merged, and are hard to read. Currently using a formula like this: =A3&" Phone: "&B3&" Fax: "&C3 I am tired of trying to get it to work, and can't seem to find the answer here. Thank you in advance! One way: =A3 & TEXT(B3,""" Phone:...

Two cells divided into next cell
Hope this makes sense. I have F column that totals down to F16. Then I have a total on D16. I need both totals to be divided and show up on G16. Every cell has $ Amts, but the only cells not totaled down the row are E,G and J. So G16 is blank. Is this possible to do and If so how. I don't even know the formula to Divide and I need this to automatically happen every time I Insert the Amts in the cells. Please help In G16: =D16/F16 "Day" <Day@discussions.microsoft.com> wrote in message news:62C49FCC-273A-425B-9EEF-DCF2109A0DD9@microsoft.com... > > ...

Macro to copy cell down
I want to create a macro that will copy the cell above to the active cell. OR the built-in key that will do that tenbob@optonline.net Bob A few seconds with the macro recorder set to "Relative Reference" gave me this. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 11/3/2003 by Gord Dibben ' ' Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False End Sub Gord Dibben XL2002 On Tue, 04 Nov 2003 04:26:28 GMT, Bob K. <tenbob@optonline.net> wrote: >I want to create a mac...

Outlook 2002 error (0x80040119) HELP!
How do I reinstall Outlook 2002 so it doesn't follow the same path that when installed doesn't work? I don't want to have to reformat the drive and start over. I have an issue/error 0X80040119 and am unable to send or receive mail. Looks like I am sending or receiving mail then before it finishes up comes this error. HELP Likely there is some issue with the PST file. Run SCANPST.EXE on the current PST file, and run scandisk on your hard drive to check for disk errors. Last resort, create a new PST file and set it as the default delivery location. On Tue, 18 Nov 2003 15:38:...

get data form cells sequentially
Hi there, I have a spreadsheet i am creating which is giving me some amount o hassle!!! I'm sure it is possible to do this but i just cant figure ou how:- I have one worksheet which has many copies of the same table, eac table is consistantly ontop of the other i.e. first table uses cells A - G10 (a rectangle of cells). The dates i am trying to use are in a pattern of a nine row seperatio i.e. first date is in cell G2, next one is G11, next one G20, and s on. I need to find out how i can make a formula which i could copy whic would grab the date out of these cells in sequence for pl...