Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i get a cell to fill red/blue as the outcome of a formula?
I want to get a cell to fill in a particular colour to indicate if one of my
pupils is working ahead or on target. I want excell to execute a check and fill the cell automatically with the appropriate colour. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i get a cell to fill red/blue as the outcome of a formula?
You would use Conditional Formatting for this. If you would like
further (more detailed) guidance then please tell us a bit about what data you have, how it is laid out, and in what circumstances you want the colour to change. Pete On Jan 27, 1:05*pm, Lynz1976 wrote: I want to get a cell to fill in a particular colour to indicate if one of my pupils is working ahead or on target. I want excell to execute a check and fill the cell automatically with the appropriate colour. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i get a cell to fill red/blue as the outcome of a formula?
Hi,
Use conditional formatting http://www.contextures.com/xlCondFormat01.html -- _______________________ Click "Yes" if it helps ________ Thanks Suleman Peerzade "Lynz1976" wrote: I want to get a cell to fill in a particular colour to indicate if one of my pupils is working ahead or on target. I want excell to execute a check and fill the cell automatically with the appropriate colour. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i get a cell to fill red/blue as the outcome of a formu
Hi I will be entering a grade into a cell ie Cc or Cb etc. This I will check
against the target grade for the pupil using an if statement or something similar. This will then return a value which I would like to trigger the colour of the cell. i.e. if the student its below target Red on target Yellow ahead of target Green. Can you help with this? Thanks Lyndsey "Pete_UK" wrote: You would use Conditional Formatting for this. If you would like further (more detailed) guidance then please tell us a bit about what data you have, how it is laid out, and in what circumstances you want the colour to change. Pete On Jan 27, 1:05 pm, Lynz1976 wrote: I want to get a cell to fill in a particular colour to indicate if one of my pupils is working ahead or on target. I want excell to execute a check and fill the cell automatically with the appropriate colour. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i get a cell to fill red/blue as the outcome of a formu
I assume you will have pupils listed on different rows, with data
going across for each pupil. Which cell/column will hold the target grade and which cell/column for the actual grade? I presume you just want the actual grade cell to change colour? Pete On Jan 27, 4:00*pm, Lynz1976 wrote: Hi I will be entering a grade into a cell ie Cc or Cb etc. This I will check against the target grade for the pupil using an if statement or something similar. This will then return a value which I would like to trigger the colour of the cell. i.e. if the student its below target Red on target Yellow ahead of target Green. Can you help with this? Thanks Lyndsey "Pete_UK" wrote: You would use Conditional Formatting for this. If you would like further (more detailed) guidance then please tell us a bit about what data you have, how it is laid out, and in what circumstances you want the colour to change. Pete On Jan 27, 1:05 pm, Lynz1976 wrote: I want to get a cell to fill in a particular colour to indicate if one of my pupils is working ahead or on target. I want excell to execute a check and fill the cell automatically with the appropriate colour.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i get a cell to fill red/blue as the outcome of a formu
Yes each pupil will have their own row of information. Im not sure yet
exactly which cell will hold the target and which the current grade but lets say for arguments sake the target grade cell is C2 and the current grade cell is D2. Yes the cell that the current grade is in can change colour that will be great. Thanks Lyndsey "Pete_UK" wrote: I assume you will have pupils listed on different rows, with data going across for each pupil. Which cell/column will hold the target grade and which cell/column for the actual grade? I presume you just want the actual grade cell to change colour? Pete On Jan 27, 4:00 pm, Lynz1976 wrote: Hi I will be entering a grade into a cell ie Cc or Cb etc. This I will check against the target grade for the pupil using an if statement or something similar. This will then return a value which I would like to trigger the colour of the cell. i.e. if the student its below target Red on target Yellow ahead of target Green. Can you help with this? Thanks Lyndsey "Pete_UK" wrote: You would use Conditional Formatting for this. If you would like further (more detailed) guidance then please tell us a bit about what data you have, how it is laid out, and in what circumstances you want the colour to change. Pete On Jan 27, 1:05 pm, Lynz1976 wrote: I want to get a cell to fill in a particular colour to indicate if one of my pupils is working ahead or on target. I want excell to execute a check and fill the cell automatically with the appropriate colour.- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i get a cell to fill red/blue as the outcome of a formu
Okay, well assume you have a header row, so that your first pupil's
data is in row 2. Highlight from cells D2 down to the bottom of your data, then click on Format | Conditional Format - a panel pops up. In the first box of this you should select Formula Is rather than Cell Value Is, and then in the next box you should enter this formula: =AND(D2<"",D2C2) Then click on the Format button and select the Patterns tab (for background colour) and choose Green, then click OK to get back to the first dialogue box. Click Add to set up the next condition, choose Formula Is again, and enter this formula: =AND(D2<"",D2=C2) This time click on the Format button, Patterns tab and choose Yellow (for on target), then click OK. Click Add once more to set up the final condition, Formula Is, and enter this formula: =AND(D2<"",D2<C2) This time you need to choose Red in the format boxes, then click OK twice to remove the dialogues. Excel will have adjusted the cell references to suit the range that you had highlighted, so you can test it out by putting a value in one of the C cells and vary values in the corresponding D cell. Hope this helps. Pete On Jan 27, 4:37*pm, Lynz1976 wrote: Yes each pupil will have their own row of information. Im not sure yet exactly which cell will hold the target and which the current grade but lets say for arguments sake the target grade cell is C2 and the current grade cell is D2. Yes the cell that the current grade is in can change colour that will be great. Thanks Lyndsey "Pete_UK" wrote: I assume you will have pupils listed on different rows, with data going across for each pupil. Which cell/column will hold the target grade and which cell/column for the actual grade? I presume you just want the actual grade cell to change colour? Pete On Jan 27, 4:00 pm, Lynz1976 wrote: Hi I will be entering a grade into a cell ie Cc or Cb etc. This I will check against the target grade for the pupil using an if statement or something similar. This will then return a value which I would like to trigger the colour of the cell. i.e. if the student its below target Red on target Yellow ahead of target Green. Can you help with this? Thanks Lyndsey "Pete_UK" wrote: You would use Conditional Formatting for this. If you would like further (more detailed) guidance then please tell us a bit about what data you have, how it is laid out, and in what circumstances you want the colour to change. Pete On Jan 27, 1:05 pm, Lynz1976 wrote: I want to get a cell to fill in a particular colour to indicate if one of my pupils is working ahead or on target. I want excell to execute a check and fill the cell automatically with the appropriate colour.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i get a cell to fill red/blue as the outcome of a formu
Thanks I will have a look :-)
"Suleman Peerzade" wrote: Hi, Use conditional formatting http://www.contextures.com/xlCondFormat01.html -- _______________________ Click "Yes" if it helps ________ Thanks Suleman Peerzade "Lynz1976" wrote: I want to get a cell to fill in a particular colour to indicate if one of my pupils is working ahead or on target. I want excell to execute a check and fill the cell automatically with the appropriate colour. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i get a cell to fill red/blue as the outcome of a formu
Thanks that was really help full I have it working for letter grades now. I
have another question if you dont mind?? I also have to do it for combined number and letter grades i.e. 6c or 6b or 6a etc. I cant get it to work for that? Do you know why and how I can make it work? Many thanks Lyndsey "Pete_UK" wrote: Okay, well assume you have a header row, so that your first pupil's data is in row 2. Highlight from cells D2 down to the bottom of your data, then click on Format | Conditional Format - a panel pops up. In the first box of this you should select Formula Is rather than Cell Value Is, and then in the next box you should enter this formula: =AND(D2<"",D2C2) Then click on the Format button and select the Patterns tab (for background colour) and choose Green, then click OK to get back to the first dialogue box. Click Add to set up the next condition, choose Formula Is again, and enter this formula: =AND(D2<"",D2=C2) This time click on the Format button, Patterns tab and choose Yellow (for on target), then click OK. Click Add once more to set up the final condition, Formula Is, and enter this formula: =AND(D2<"",D2<C2) This time you need to choose Red in the format boxes, then click OK twice to remove the dialogues. Excel will have adjusted the cell references to suit the range that you had highlighted, so you can test it out by putting a value in one of the C cells and vary values in the corresponding D cell. Hope this helps. Pete On Jan 27, 4:37 pm, Lynz1976 wrote: Yes each pupil will have their own row of information. Im not sure yet exactly which cell will hold the target and which the current grade but lets say for arguments sake the target grade cell is C2 and the current grade cell is D2. Yes the cell that the current grade is in can change colour that will be great. Thanks Lyndsey "Pete_UK" wrote: I assume you will have pupils listed on different rows, with data going across for each pupil. Which cell/column will hold the target grade and which cell/column for the actual grade? I presume you just want the actual grade cell to change colour? Pete On Jan 27, 4:00 pm, Lynz1976 wrote: Hi I will be entering a grade into a cell ie Cc or Cb etc. This I will check against the target grade for the pupil using an if statement or something similar. This will then return a value which I would like to trigger the colour of the cell. i.e. if the student its below target Red on target Yellow ahead of target Green. Can you help with this? Thanks Lyndsey "Pete_UK" wrote: You would use Conditional Formatting for this. If you would like further (more detailed) guidance then please tell us a bit about what data you have, how it is laid out, and in what circumstances you want the colour to change. Pete On Jan 27, 1:05 pm, Lynz1976 wrote: I want to get a cell to fill in a particular colour to indicate if one of my pupils is working ahead or on target. I want excell to execute a check and fill the cell automatically with the appropriate colour.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i get a cell to fill red/blue as the outcome of a formu
Glad to hear that it worked for you.
What are the rules for these combined grades? Is 6a considered to be better than 6b and 6c ? The problem with this is that c is a "larger" letter than a, and so it is "greater than". There are ways of coping with this, eg by converting those grades to a number - this number doesn't really mean anything, but it just represents the sequence that the grades should be taken in. For this to work you will need to list all possible grades in the appropriate sequence. Can you post them here, and I'll advise you further tomorrow? Pete On Jan 27, 9:00*pm, Lynz1976 wrote: Thanks that was really help full I have it working for letter grades now. I have another question if you dont mind?? I also have to do it for combined number and letter grades i.e. 6c or 6b or 6a etc. I cant get it to work for that? Do you know why and how I can make it work? Many thanks Lyndsey "Pete_UK" wrote: Okay, well assume you have a header row, so that your first pupil's data is in row 2. Highlight from cells D2 down to the bottom of your data, then click on Format | Conditional Format - a panel pops up. In the first box of this you should select Formula Is rather than Cell Value Is, and then in the next box you should enter this formula: =AND(D2<"",D2C2) Then click on the Format button and select the Patterns tab (for background colour) and choose Green, then click OK to get back to the first dialogue box. Click Add to set up the next condition, choose Formula Is again, and enter this formula: =AND(D2<"",D2=C2) This time click on the Format button, Patterns tab and choose Yellow (for on target), then click OK. Click Add once more to set up the final condition, Formula Is, and enter this formula: =AND(D2<"",D2<C2) This time you need to choose Red in the format boxes, then click OK twice to remove the dialogues. Excel will have adjusted the cell references to suit the range that you had highlighted, so you can test it out by putting a value in one of the C cells and vary values in the corresponding D cell. Hope this helps. Pete On Jan 27, 4:37 pm, Lynz1976 wrote: Yes each pupil will have their own row of information. Im not sure yet exactly which cell will hold the target and which the current grade but lets say for arguments sake the target grade cell is C2 and the current grade cell is D2. Yes the cell that the current grade is in can change colour that will be great. Thanks Lyndsey "Pete_UK" wrote: I assume you will have pupils listed on different rows, with data going across for each pupil. Which cell/column will hold the target grade and which cell/column for the actual grade? I presume you just want the actual grade cell to change colour? Pete On Jan 27, 4:00 pm, Lynz1976 wrote: Hi I will be entering a grade into a cell ie Cc or Cb etc. This I will check against the target grade for the pupil using an if statement or something similar. This will then return a value which I would like to trigger the colour of the cell. i.e. if the student its below target Red on target Yellow ahead of target Green. Can you help with this? Thanks Lyndsey "Pete_UK" wrote: You would use Conditional Formatting for this. If you would like further (more detailed) guidance then please tell us a bit about what data you have, how it is laid out, and in what circumstances you want the colour to change. Pete On Jan 27, 1:05 pm, Lynz1976 wrote: I want to get a cell to fill in a particular colour to indicate if one of my pupils is working ahead or on target. I want excell to execute a check and fill the cell automatically with the appropriate colour.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i get a cell to fill red/blue as the outcome of a formu
They go 3c, 3b, 3a, 4c, 4b, 4a etc all the way up to 8a, a being higher than
c and 8a being the best 3c being the worst. Thanks for your help with this. Lyndsey "Pete_UK" wrote: Glad to hear that it worked for you. What are the rules for these combined grades? Is 6a considered to be better than 6b and 6c ? The problem with this is that c is a "larger" letter than a, and so it is "greater than". There are ways of coping with this, eg by converting those grades to a number - this number doesn't really mean anything, but it just represents the sequence that the grades should be taken in. For this to work you will need to list all possible grades in the appropriate sequence. Can you post them here, and I'll advise you further tomorrow? Pete On Jan 27, 9:00 pm, Lynz1976 wrote: Thanks that was really help full I have it working for letter grades now. I have another question if you dont mind?? I also have to do it for combined number and letter grades i.e. 6c or 6b or 6a etc. I cant get it to work for that? Do you know why and how I can make it work? Many thanks Lyndsey "Pete_UK" wrote: Okay, well assume you have a header row, so that your first pupil's data is in row 2. Highlight from cells D2 down to the bottom of your data, then click on Format | Conditional Format - a panel pops up. In the first box of this you should select Formula Is rather than Cell Value Is, and then in the next box you should enter this formula: =AND(D2<"",D2C2) Then click on the Format button and select the Patterns tab (for background colour) and choose Green, then click OK to get back to the first dialogue box. Click Add to set up the next condition, choose Formula Is again, and enter this formula: =AND(D2<"",D2=C2) This time click on the Format button, Patterns tab and choose Yellow (for on target), then click OK. Click Add once more to set up the final condition, Formula Is, and enter this formula: =AND(D2<"",D2<C2) This time you need to choose Red in the format boxes, then click OK twice to remove the dialogues. Excel will have adjusted the cell references to suit the range that you had highlighted, so you can test it out by putting a value in one of the C cells and vary values in the corresponding D cell. Hope this helps. Pete On Jan 27, 4:37 pm, Lynz1976 wrote: Yes each pupil will have their own row of information. Im not sure yet exactly which cell will hold the target and which the current grade but lets say for arguments sake the target grade cell is C2 and the current grade cell is D2. Yes the cell that the current grade is in can change colour that will be great. Thanks Lyndsey "Pete_UK" wrote: I assume you will have pupils listed on different rows, with data going across for each pupil. Which cell/column will hold the target grade and which cell/column for the actual grade? I presume you just want the actual grade cell to change colour? Pete On Jan 27, 4:00 pm, Lynz1976 wrote: Hi I will be entering a grade into a cell ie Cc or Cb etc. This I will check against the target grade for the pupil using an if statement or something similar. This will then return a value which I would like to trigger the colour of the cell. i.e. if the student its below target Red on target Yellow ahead of target Green. Can you help with this? Thanks Lyndsey "Pete_UK" wrote: You would use Conditional Formatting for this. If you would like further (more detailed) guidance then please tell us a bit about what data you have, how it is laid out, and in what circumstances you want the colour to change. Pete On Jan 27, 1:05 pm, Lynz1976 wrote: I want to get a cell to fill in a particular colour to indicate if one of my pupils is working ahead or on target. I want excell to execute a check and fill the cell automatically with the appropriate colour.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i get a cell to fill red/blue as the outcome of a formu
Good morning Lyndsey,
you obviously start earlier than I do !! To make the formulae shorter, I would suggest that you put those grades in the correct sequence (lowest first) in an out of the way part of your sheet. I put these in Z1:Z18: 3c 3b 3a 4c 4b 4a 5c 5b 5a 6c 6b 6a 7c 7b 7a 8c 8b 8a Then I highlighted this range and clicked on Insert | Names | Define and then named this range "grades" (without the quotes). Then you need to go through the same procedure as before, but with different formulae. So, highlight cells from D2 down to however many you think you need, and click on Format | Conditional Formatting. Each time you will need to select Formula Is, and this time your formula will be: =AND(D2<"",MATCH(D2,grades,0)MATCH(C2,grades,0)) for the green condition: =AND(D2<"",D2=C2) for the yellow condition (unchanged), and: =AND(D2<"",MATCH(D2,grades,0)<MATCH(C2,grades,0)) for the red condition. Click OK twice, and then test it out by putting, say, 4b in C2 and then varying D2. Hope this helps. Pete On Jan 28, 6:49*am, Lynz1976 wrote: They go 3c, 3b, 3a, 4c, 4b, 4a etc all the way up to 8a, a being higher than c and 8a being the best 3c being the worst. Thanks for your help with this. Lyndsey "Pete_UK" wrote: Glad to hear that it worked for you. What are the rules for these combined grades? Is 6a considered to be better than 6b and 6c ? The problem with this is that c is a "larger" letter than a, and so it is "greater than". There are ways of coping with this, eg by converting those grades to a number - this number doesn't really mean anything, but it just represents the sequence that the grades should be taken in. For this to work you will need to list all possible grades in the appropriate sequence. Can you post them here, and I'll advise you further tomorrow? Pete On Jan 27, 9:00 pm, Lynz1976 wrote: Thanks that was really help full I have it working for letter grades now. I have another question if you dont mind?? I also have to do it for combined number and letter grades i.e. 6c or 6b or 6a etc. I cant get it to work for that? Do you know why and how I can make it work? Many thanks Lyndsey "Pete_UK" wrote: Okay, well assume you have a header row, so that your first pupil's data is in row 2. Highlight from cells D2 down to the bottom of your data, then click on Format | Conditional Format - a panel pops up. In the first box of this you should select Formula Is rather than Cell Value Is, and then in the next box you should enter this formula: =AND(D2<"",D2C2) Then click on the Format button and select the Patterns tab (for background colour) and choose Green, then click OK to get back to the first dialogue box. Click Add to set up the next condition, choose Formula Is again, and enter this formula: =AND(D2<"",D2=C2) This time click on the Format button, Patterns tab and choose Yellow (for on target), then click OK. Click Add once more to set up the final condition, Formula Is, and enter this formula: =AND(D2<"",D2<C2) This time you need to choose Red in the format boxes, then click OK twice to remove the dialogues. Excel will have adjusted the cell references to suit the range that you had highlighted, so you can test it out by putting a value in one of the C cells and vary values in the corresponding D cell. Hope this helps. Pete On Jan 27, 4:37 pm, Lynz1976 wrote: Yes each pupil will have their own row of information. Im not sure yet exactly which cell will hold the target and which the current grade but lets say for arguments sake the target grade cell is C2 and the current grade cell is D2. Yes the cell that the current grade is in can change colour that will be great. Thanks Lyndsey "Pete_UK" wrote: I assume you will have pupils listed on different rows, with data going across for each pupil. Which cell/column will hold the target grade and which cell/column for the actual grade? I presume you just want the actual grade cell to change colour? Pete On Jan 27, 4:00 pm, Lynz1976 wrote: Hi I will be entering a grade into a cell ie Cc or Cb etc. This I will check against the target grade for the pupil using an if statement or something similar. This will then return a value which I would like to trigger the colour of the cell. i.e. if the student its below target Red on target Yellow ahead of target Green. Can you help with this? Thanks Lyndsey "Pete_UK" wrote: You would use Conditional Formatting for this. If you would like further (more detailed) guidance then please tell us a bit about what data you have, how it is laid out, and in what circumstances you want the colour to change. Pete On Jan 27, 1:05 pm, Lynz1976 wrote: I want to get a cell to fill in a particular colour to indicate if one of my pupils is working ahead or on target. I want excell to execute a check and fill the cell automatically with the appropriate colour.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i get a cell to fill red/blue as the outcome of a formu
Sorry I only just got back onto this I got side tracked!! Thats worked really
well thanks for all your help. Lyndsey "Pete_UK" wrote: Good morning Lyndsey, you obviously start earlier than I do !! To make the formulae shorter, I would suggest that you put those grades in the correct sequence (lowest first) in an out of the way part of your sheet. I put these in Z1:Z18: 3c 3b 3a 4c 4b 4a 5c 5b 5a 6c 6b 6a 7c 7b 7a 8c 8b 8a Then I highlighted this range and clicked on Insert | Names | Define and then named this range "grades" (without the quotes). Then you need to go through the same procedure as before, but with different formulae. So, highlight cells from D2 down to however many you think you need, and click on Format | Conditional Formatting. Each time you will need to select Formula Is, and this time your formula will be: =AND(D2<"",MATCH(D2,grades,0)MATCH(C2,grades,0)) for the green condition: =AND(D2<"",D2=C2) for the yellow condition (unchanged), and: =AND(D2<"",MATCH(D2,grades,0)<MATCH(C2,grades,0)) for the red condition. Click OK twice, and then test it out by putting, say, 4b in C2 and then varying D2. Hope this helps. Pete On Jan 28, 6:49 am, Lynz1976 wrote: They go 3c, 3b, 3a, 4c, 4b, 4a etc all the way up to 8a, a being higher than c and 8a being the best 3c being the worst. Thanks for your help with this. Lyndsey "Pete_UK" wrote: Glad to hear that it worked for you. What are the rules for these combined grades? Is 6a considered to be better than 6b and 6c ? The problem with this is that c is a "larger" letter than a, and so it is "greater than". There are ways of coping with this, eg by converting those grades to a number - this number doesn't really mean anything, but it just represents the sequence that the grades should be taken in. For this to work you will need to list all possible grades in the appropriate sequence. Can you post them here, and I'll advise you further tomorrow? Pete On Jan 27, 9:00 pm, Lynz1976 wrote: Thanks that was really help full I have it working for letter grades now. I have another question if you dont mind?? I also have to do it for combined number and letter grades i.e. 6c or 6b or 6a etc. I cant get it to work for that? Do you know why and how I can make it work? Many thanks Lyndsey "Pete_UK" wrote: Okay, well assume you have a header row, so that your first pupil's data is in row 2. Highlight from cells D2 down to the bottom of your data, then click on Format | Conditional Format - a panel pops up. In the first box of this you should select Formula Is rather than Cell Value Is, and then in the next box you should enter this formula: =AND(D2<"",D2C2) Then click on the Format button and select the Patterns tab (for background colour) and choose Green, then click OK to get back to the first dialogue box. Click Add to set up the next condition, choose Formula Is again, and enter this formula: =AND(D2<"",D2=C2) This time click on the Format button, Patterns tab and choose Yellow (for on target), then click OK. Click Add once more to set up the final condition, Formula Is, and enter this formula: =AND(D2<"",D2<C2) This time you need to choose Red in the format boxes, then click OK twice to remove the dialogues. Excel will have adjusted the cell references to suit the range that you had highlighted, so you can test it out by putting a value in one of the C cells and vary values in the corresponding D cell. Hope this helps. Pete On Jan 27, 4:37 pm, Lynz1976 wrote: Yes each pupil will have their own row of information. Im not sure yet exactly which cell will hold the target and which the current grade but lets say for arguments sake the target grade cell is C2 and the current grade cell is D2. Yes the cell that the current grade is in can change colour that will be great. Thanks Lyndsey "Pete_UK" wrote: I assume you will have pupils listed on different rows, with data going across for each pupil. Which cell/column will hold the target grade and which cell/column for the actual grade? I presume you just want the actual grade cell to change colour? Pete On Jan 27, 4:00 pm, Lynz1976 wrote: Hi I will be entering a grade into a cell ie Cc or Cb etc. This I will check against the target grade for the pupil using an if statement or something similar. This will then return a value which I would like to trigger the colour of the cell. i.e. if the student its below target Red on target Yellow ahead of target Green. Can you help with this? Thanks Lyndsey "Pete_UK" wrote: You would use Conditional Formatting for this. If you would like further (more detailed) guidance then please tell us a bit about what data you have, how it is laid out, and in what circumstances you want the colour to change. Pete On Jan 27, 1:05 pm, Lynz1976 wrote: I want to get a cell to fill in a particular colour to indicate if one of my pupils is working ahead or on target. I want excell to execute a check and fill the cell automatically with the appropriate colour.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i get a cell to fill red/blue as the outcome of a formu
Sorry I only just got back onto this again got side tracked!! Thats worked
really well thank you for all your help. Lyndsey "Pete_UK" wrote: Good morning Lyndsey, you obviously start earlier than I do !! To make the formulae shorter, I would suggest that you put those grades in the correct sequence (lowest first) in an out of the way part of your sheet. I put these in Z1:Z18: 3c 3b 3a 4c 4b 4a 5c 5b 5a 6c 6b 6a 7c 7b 7a 8c 8b 8a Then I highlighted this range and clicked on Insert | Names | Define and then named this range "grades" (without the quotes). Then you need to go through the same procedure as before, but with different formulae. So, highlight cells from D2 down to however many you think you need, and click on Format | Conditional Formatting. Each time you will need to select Formula Is, and this time your formula will be: =AND(D2<"",MATCH(D2,grades,0)MATCH(C2,grades,0)) for the green condition: =AND(D2<"",D2=C2) for the yellow condition (unchanged), and: =AND(D2<"",MATCH(D2,grades,0)<MATCH(C2,grades,0)) for the red condition. Click OK twice, and then test it out by putting, say, 4b in C2 and then varying D2. Hope this helps. Pete On Jan 28, 6:49 am, Lynz1976 wrote: They go 3c, 3b, 3a, 4c, 4b, 4a etc all the way up to 8a, a being higher than c and 8a being the best 3c being the worst. Thanks for your help with this. Lyndsey "Pete_UK" wrote: Glad to hear that it worked for you. What are the rules for these combined grades? Is 6a considered to be better than 6b and 6c ? The problem with this is that c is a "larger" letter than a, and so it is "greater than". There are ways of coping with this, eg by converting those grades to a number - this number doesn't really mean anything, but it just represents the sequence that the grades should be taken in. For this to work you will need to list all possible grades in the appropriate sequence. Can you post them here, and I'll advise you further tomorrow? Pete On Jan 27, 9:00 pm, Lynz1976 wrote: Thanks that was really help full I have it working for letter grades now. I have another question if you dont mind?? I also have to do it for combined number and letter grades i.e. 6c or 6b or 6a etc. I cant get it to work for that? Do you know why and how I can make it work? Many thanks Lyndsey "Pete_UK" wrote: Okay, well assume you have a header row, so that your first pupil's data is in row 2. Highlight from cells D2 down to the bottom of your data, then click on Format | Conditional Format - a panel pops up. In the first box of this you should select Formula Is rather than Cell Value Is, and then in the next box you should enter this formula: =AND(D2<"",D2C2) Then click on the Format button and select the Patterns tab (for background colour) and choose Green, then click OK to get back to the first dialogue box. Click Add to set up the next condition, choose Formula Is again, and enter this formula: =AND(D2<"",D2=C2) This time click on the Format button, Patterns tab and choose Yellow (for on target), then click OK. Click Add once more to set up the final condition, Formula Is, and enter this formula: =AND(D2<"",D2<C2) This time you need to choose Red in the format boxes, then click OK twice to remove the dialogues. Excel will have adjusted the cell references to suit the range that you had highlighted, so you can test it out by putting a value in one of the C cells and vary values in the corresponding D cell. Hope this helps. Pete On Jan 27, 4:37 pm, Lynz1976 wrote: Yes each pupil will have their own row of information. Im not sure yet exactly which cell will hold the target and which the current grade but lets say for arguments sake the target grade cell is C2 and the current grade cell is D2. Yes the cell that the current grade is in can change colour that will be great. Thanks Lyndsey "Pete_UK" wrote: I assume you will have pupils listed on different rows, with data going across for each pupil. Which cell/column will hold the target grade and which cell/column for the actual grade? I presume you just want the actual grade cell to change colour? Pete On Jan 27, 4:00 pm, Lynz1976 wrote: Hi I will be entering a grade into a cell ie Cc or Cb etc. This I will check against the target grade for the pupil using an if statement or something similar. This will then return a value which I would like to trigger the colour of the cell. i.e. if the student its below target Red on target Yellow ahead of target Green. Can you help with this? Thanks Lyndsey "Pete_UK" wrote: You would use Conditional Formatting for this. If you would like further (more detailed) guidance then please tell us a bit about what data you have, how it is laid out, and in what circumstances you want the colour to change. Pete On Jan 27, 1:05 pm, Lynz1976 wrote: I want to get a cell to fill in a particular colour to indicate if one of my pupils is working ahead or on target. I want excell to execute a check and fill the cell automatically with the appropriate colour.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locking cell depending on outcome of formulae | Excel Discussion (Misc queries) | |||
creating outcome series from formula | Excel Worksheet Functions | |||
wrong calculation in excel? formula outcome < cell value?? | Excel Worksheet Functions | |||
format the outcome of a formula | Excel Discussion (Misc queries) | |||
Stop % showing in outcome of formula | Excel Worksheet Functions |