Conditional Formatting

Topic

New Reply

I want to use conditional formatting on a formula not on the result of the formula. In other words, I’m teaching my students how to write formulas and use functions. I know they can look through a column of numbers and find the highest number. I want to know if they put the formula (=MAX(range) in the cell. I know I can use the CTRL + ` to toggle the display of the formulas but the easiest thing will be for me to turn the cell green when they put the correct formula in the cell. My idea is that I will have the cells turned red where they are supposed to put a formula. It will turn green when they have done it correctly. Stated another way, if the highest number is 89, they could put 89 in the cell and that would be wrong and the cell should stay red. If they put “=MAX(range)” in the cell and the result is 89, that would be correct and the cell should turn green. I have asked several people and looked in online help and on the web to no avail. Thanks for any help on this one.

Reply | Quote

Replies

Select A1
Insert- name -define

Name:
GetFormula
Refers to:
=GET.CELL(6,A1)
[OK]

Select the cell of interest and make the background red
format – Conditional formatting
formula is:
=GetFormula=”=MAX(A1:A10)”
Format
pattern(tab)
Choose the green background
[ok][ok]

Adjust the formula as needed. You can include an OR if desired…

Steve

Reply | Quote

Steve, it took me a while to figure out where the different parts go but I finally got it. The named range must be at the top of the cells for the range in the MAX function argument. I had to go online and see what the first argument (6) was all about but I understand now. Thanks a million.

Reply | Quote

As long as the numbers I want to run a function on with my formula are in a continous range, this seems to work. However, I have results at the bottom of columns in (B35:D38) and again in (G35:I38). I want to compare these monthly results in (K35:M38) to determine which month has higher results. In other words I want to have the students put in K35 the formula “=G35-B35”. Since there are labels and a blank column between D and G (columns E & F), when I name the range in B35 and then put the Conditional Formatting rule in K35, it does not turn my cell green. Any thoughts on that?

Reply | Quote

I am afraid I don’t follow the question (I am a littly “iffy” on how it even relates to the original question). Could you post an example file and explain what you want/need?

Steve

Reply | Quote

Hopefully this will help clarify things. The cells that have turned green are working as expected. The ones that are still blue aren’t working. I’ve named a range in B35 and put conditional formatting in K35 and L35.

Reply | Quote

Change the GetChange to the refers to:
=GET.FORMULA(Sheet1!K41)

[Note: This uses RC referencing which makes all the formulas identical.]

Then select K35:M38 and set the cond format to:
=GetChange=”=RC[-4]-RC[-9]”

Steve

Reply | Quote

That works. I don’t know how or why but it works. I don’t get the Get.Formula thing since that cell is empty. The RC thing makes sense as a relative reference to take the cell 4 to my left and subtract the cell 9 to my left. I see that however this works, it is letting me use the same formula in all those cells in the K35:M38 range but I don’t see what K41 has to do with anything. Is the Get.Formula a VBA thing? I’m not finding much about it online. Thanks again.

Now, I suppose I should protect my sheet after I take out the correct formulas so that some bright student doesn’t just go to Conditional Formatting and see what formula I have for the cells. I suppose if they know enough about Excel to do that, they probably already know how to do these simple formulas. I’m going to try this again in a week or two with more complicated functions like FV() and NPER() and nested IF(). Hopefully I’ll be able to apply what I’ve learned here to a new spreadsheet.

Reply | Quote

I happened to have K41 selected when I copied the named refers to. Typically you would enter this this based on the selected cell at the time…

I apologize, I should have made that more clear…

Get.Formula and Get.Cell are old functions from macro pages in Excel (before the introduction of VB) and only remain for legacy/compatibility purposes (though they do allow these types of things…) You can download the help file (macrofun.hlp) from http://support.microsoft.com/kb/128185

Steve

Reply | Quote

I downloaded the help files. Thanks again for your help.

Reply | Quote

I have my spreadsheet working almost the way I want it to work and I’ve applied what I’ve learned to another one as well. The problem I’ve encountered now is that if a student puts the correct formula in a cell at the bottom of a column (=sum(D3:D32)), the cells changes colors as I wanted it to. However, if the student copies that cell and pastes it somewhere else or if the student uses the little box at the right hand corner to copy the cell to the right I find that this copies my conditional formatting as well. In another column, I may want them to use a different formula and have the conditional formatting for that cell setup to recognize that desired formula (=E34/F34). The problem is that my conditional formatting is being overwritten when the students copy right and then the cell turns green so they think they have done it correctly. I have protected my sheet but I don’t see a way to keep them from being able to copy the correct formula from one cell and pasting it into another cell where it should be incorrect but it looks like it is correct because the cell turns the ‘right’ color for them. If you look in the attached file, I hope this will make sense.

Reply | Quote

I don’t think there is any simple workaround: copying cells copies the formatting and conditional formatting is a format. To prevent this would require some protection of the sheet which would seem to defeat the purpose of the user editing and adding a formula.

Steve

Reply | Quote

Another way to do this might be to have an “answer key” worksheet – that is locked and protected. Have the conditional formatting in that worksheet. The students can copy/paste all they want in the “working” worksheet and compare their results in the answer key.

Reply | Quote

I’m not sure I follow you here. It seems like you are saying to take out the conditional formatting in the ‘working’ sheet and put it into an ‘answer’ sheet. I’m not sure what the students would copy and paste into the answer sheet? Are you thinking I would have instructions that say to do all their work in the working sheet and when they are finished copy all the cells with formulas and paste those into the ‘answer’ sheet? If they can copy and paste into the ‘answer’ sheet, aren’t I back to the same problem expressed earlier today? If I lock the cells to prevent the conditional formatting from being able to get changed, I think I will preclude them from being able to paste into that cell. If I let them paste into a cell, it doesn’t seem that I can prevent the conditional formatting rules from changing.

Reply | Quote

This is a sample of what I was thinking of.
I haven’t protected the answer key sheet

Reply | Quote

I think you might be on to something that can help me but I don’t know what you did. I’m still using Office 2003 so that might make a difference. I don’t know what you put in your ‘Answer’ worksheet to get the cell to say ‘True’ and turn yellow. If I copy the cell from the ‘working’ sheet and paste it into the ‘answer’ sheet, it just shows 0 because there are no numbers in the A column there. I hope I’m not missing the forrest for the trees here.

Reply | Quote

Oops here is the same workbook in 2003 format (make sure you enable macros to use the workbook).

It uses the same Named formula as Steve explained previously. The Get.Cell function pulls the formula as text from the same cell in the worksheet sheet. The formula compares the pulled formula with the formula you are looking for. If the two formulas are the same, the result is TRUE and the colour of the cell is green (or um light yellow on your machine – colours may vary apparently).

Reply | Quote

I feel like I’m really dense here but I’m just not getting this at all. Why are the cells in the ‘answer’ sheet yellow and where did true come from? When I look at the conditional formatting it says, “If the cell value = True, turn the cell yellow.” I have no idea why the cell says true. Additionally, if I click on the cell I see that it has “=GetForumula=”=max(a2:a10)”” in it. Doesn’t that want to be in the conditional formatting? How would that get in there? If I put that in there then all the students need to do is look to see what I’ve put in there and then put the same in theirs. That would kind of defeat the purpose, wouldn’t it? I’m sure this is my lack of familiarity with this but I just don’t see it. I know you are trying to help. Where is the Get.Cell function in this sheet? I see the Named Region of GetForumula. Perhaps that is the piece I’m missing.

Reply | Quote

The “GetFormula” is a range name. On sheet AnswerKey click on cell A11. Then access the menu Insert | Name | Define… then click on GetFormula in the drop down.
The range name is equal to the formula Get.Cell(6,Worksheet!A11).
So if the cell on Worksheet A11 is equal to “=max(a2:a10)” the result is true and the conditional format is applied.

Hope this helps.

Reply | Quote

The Get.Cell function (which has been discussed in other threads – just do a search for more reading and examples) is an Excel 4 macro function. The function =GET.CELL(6,cellref) pulls the contents of the cell being referred to as text. This allows us to examine the formulas in the referenced cell as text.
Because it is an Excel 4 macro function – you can’t place it directly in a cell. You can use it within the conditional formatting (as a formula) or created a Named formula. We are kinda tricking Excel to use this old function.

If I put that in there then all the students need to do is look to see what I’ve put in there and then put the same in theirs.
With a named formula, then the hidden format can be applied to the cells, which will hide the formula from your students when you protect the answer key sheet. This is one advantage of using the named formula vs conditional formatting.

Why are the cells in the ‘answer’ sheet yellow and where did true come from?
Oops – I should have chosen a green colour, my bad. Just pick the colour you want.

and where did true come from?
As I mentioned earlier The function =GET.CELL(6,cellref) pulls the contents of the cell being referred to as text. This allows us to examine the formulas in the referenced cell as text.
What that means is the formula =GET.CELL(6,Worksheet!A11) which I gave the name =GetForumula (my typo) to will return the string “=MAX(A2:A10)”
When I compare “=MAX(A2:A10)“=”=max(a2:a10)” (excel disregards letter case) the two values are equal and so the result of the formula is TRUE
You can test this in a simpler way by typing the formula =2=2 (does 2=2) in a cell, the result will be TRUE. Change the formula to =2=3 (does 2=3) and the result will be false.

If the above makes sense to you. We can walk through creating Named Formulas in detail.

Reply | Quote

Yeah, I finally got it. I haven’t had time to look at it for the last several days but got to it tonight. After going step-by-step through your explanation, I pretty well understand. I’m not sure why the named range doesn’t have to have something in all the adjacent cells because I thought that was a rule of named ranges. Also, I don’t know why the ‘answer key’ page has to have the cells that check the formula in the same location as the cells I’m checking. I tried to hide the cells that show whether the entered formula is correct but that didn’t seem to work. Instead, I set the font and background to white regardless of whether they have it correct or incorrect. Then I created a countif() function that counts the cells with “True” in them so I already have the percentage correct without having to look at the ‘True’ or ‘False’. I can highlight those cells and see which ones people missed if it seems like some people are getting a lot of the formulas wrong but otherwise I’ll just look at the grade and enter into the gradebook. This will be a great time saver although I’ve spent a lot of time getting it set up. Now that I pretty well understand what I’m doing, I’ll apply it to some other spreadsheets and things should go smoothly. Thanks for everyone’s help.

Reply | Quote

I’m not sure why the named range doesn’t have to have something in all the adjacent cells because I thought that was a rule of named ranges.
I’m kinda foggy on this question – I think you’ll have to restate it so that I understand

I don’t know why the ‘answer key’ page has to have the cells that check the formula in the same location as the cells I’m checking.
When I built the formula =GET.CELL(6,Worksheet!A11). I had the cell A11 on the AnswerKey worksheet selected. I did this so there would be a one on one correspondence between the AnswerKey and the Worksheet. It seemed the simplest method to build the AnswerKey so that it was identical to the Worksheet.
However, it is possible when creating the formula that could refer to a different location in the Worksheet, say A10. This means that when using the formula on the AnswerKey sheet it would always refer to one row above the cell holding the formula. This can easily get confusing.

I tried to hide the cells that show whether the entered formula is correct but that didn’t seem to work
Hiding a cell, conceals the formula within the cell, not the results of a formula. With hidden cells, your students could see if they were correct or not – but not the formula you used to evaluate them.

Reply | Quote

Thanks Catherine, I think the first part is what I understood about ranges. Maybe I was incorrect in thinking that if you named a range, it could not contain any blank cells or rows. I have one blank row in my sheet but both the rows above it and below seem to work fine with the information you gave me. I understand what you mean about having the Get.Cell check the same location in the other sheet. What I’ve done is turned the font the same color as the background so that neither true or false show up in the cells. Then I do a countif function that checks for true. I’ve also put in a function to see if the student has formatted certain cells as %. Now my ‘Answer’ sheet counts all the right cells (ones with true in them on the answer sheet) and counts all the cells in the working sheet that have been formatted to percent and divides that total by the total number of possible right answers to give me their grade. It will save me a lot of time in the future and has helped me learn a few things in the process. Thanks for your help as well as other’s help.

Reply | Quote

if you named a range, it could not contain any blank cells or rows.

A named range can contain empty cells/rows/columns.

Reply | Quote

Great, I don’t know where I picked up that erroneous information but I’m glad to be corrected. You have been a big help.

Reply | Quote