Hi,
Is it possible to create an IF formula based on the colour of a cell?
I'm trying to create a staff rota. I've got some staff membes who have 2 different job roles and so 2 different rates of pay so I've colour coded their hours according to the job types.
Is there a way to use a formula to calculate the total hours in a week based on the background colour of a cell? Otherwise I'll have to manually select which cells to calculate for each job type each time.
Thanks,
Sally
Yes you can... sort of. the Get.Cell function (a historical relic buried deep in Excel) allows you to do this, within limits.
1. Every time you change cell colours you need to recalculate the sheet (F9)
2. You need to use a macro-enabled sheet.
3. You need a helper column.
After that, you need a named range (hit CTRL F3 in the attached sheet to see it); and away you go... Does this give you something to go on?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
it is possible with vba... i don't think it's possible with a regular formula. but, did you manually color code their hours? I'd suggest adding a flag for the job type instead. then you can use some simple conditional formatting and a simple sumif() formula and eliminate both manual processes...
can you upload a desensitized sample of your workbook so we can write the specific formula?
****EDIT****
wow GK... never seen that before. unfortunately i just added rep for your last post and have to spread some around before i can do so again lol.
Last edited by simarui; 04-24-2015 at 10:44 AM. Reason: response to Glenn's post
Hope I could help - if your post has been solved don't forget to mark it as such.
Clicking the * below is a great way to say thanks!
"Drowning in 10 feet of water isn?t any different than drowning in a million feet. And if you can swim, it doesn?t matter how deep the ocean is. At some level, once you realize you?re in water that?s too deep to stand, you have to have a very different approach," - Joi Ito
It is limited, but it does work well enough for relatively simple tasks. The pain is remembering to F9 it. I can't remember what day it is...
Ok, I think I've attached it right.
Sally
Oķ. It's attached. However, it's late evening here. I'll take a look at it first thing in the UK morning.
Glen, thats easy - its MarchIt is limited, but it does work well enough for relatively simple tasks. The pain is remembering to F9 it. I can't remember what day it is...
Last edited by FDibbins; 04-24-2015 at 07:24 PM.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
A UDF will do it. This thread shows it a bit.
http://www.excelforum.com/excel-form...ml#post4046090
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
Hi Glenn,
I've had a look at the attachment you sent me but at the moment it doesn't make much sense to me. I still wouldn't have any clue as to how to use that for my own formula for the colours I have on the spreadsheet I have.
I've never used macros or VBA or UDF or any of those kind of terms before so I need help in seriously basic, layman's terms to get my head around it.
Sally
OK. First step. Open the file and enable macros.
I have done this (so far) only for Sally. I removed all of the shading from the cells that will NOT form any part of the adding up. We can come up with workarounds, if you REALLY need shading in those cells. However, I used the SAME shading in the areas where the summing IS to take place as you used in the key (A3 to A7).
Using Get.Cell function, I assigned the colour numbers to those colurs (AA3 to AA7). I also manually shaded them the same as the key - just to keep my head right!!
The grey area then assigns the colours to each cell in Sally's week. (if you have more shading in Sally's week: this will get very cluttered looking, that's why I took it all out).
The formula then sums the hours worked in sally's week, if the shading in the hours worked matches the colour number appropriate to the activity.
Have a think about it & get back to me with any comments.
Don't forget, that if you change any shading of the countable numbers in Sally's week - you need to hit F9 to ensure that the results of the Get.Cell function are updated.
G
Hi,
I think I sort of understand it and that's fine that you've put the shading the same as the key and it makes sense that you've shaded the cells that actually need counting. I would, however, also prefer the cells with the hours to be in colour as well, coz I want it to obvious as soon as they look at it what type of work they're doing. As well as a copy of it for records on the computer, I want a copy to print each week to go up on the notice board.
I guess the venue can remain uncoloured, but if it's possible to have the cells with the hours coloured as well I'd prefer that, if it's not too complicated.
So my next question is, how do I take what you've done for "Sally" and get that to work for the others?
The only 2 people I really need the colour-based formula for is "Chantelle" and "Marg". My hours (Sally) don't really matter as I own the business and take my own wages out and Jenny only does instructing & Aimee only does admin.
Chantelle and Marg will be doing both instructing and admin hours (class and office based) at 2 different pay rates so I need a formula that will calculate the instructing hours (purple) and go in the cell that says "instructor" in cell W24 (Chantelle) and W29 (Marg) and a formula in cells W22 and W27 that calculates the combined hours of class admin (green) and office admin (yellow).
Is that possible to do and how do I do that?
Thanks for all your help so far. I don't really understand it beyond basic formulas/functions so I'm beyond grateful for all your help so far.
Sally
Let's take this in steps.
I guess the venue can remain uncoloured, but if it's possible to have the cells with the hours coloured as well I'd prefer that, if it's not too complicated.
OK - shade to your heart's content; but make them a DIFFERENT shade.
How do I take what you've done for "Sally" and get that to work for the others?
I'll help. What you need to do is to decide on the shading that you want and put it in place. Use ONLY the shades in the KEY to shade the countable hours. DO NOT use them anywhere else. I suggest you shade all staff. People may come and go and duties may change. It might also be nice, irrespective of the pay rates to know who's spending what hours on Admin, etc.
Calculating pay.
I'm not clear if the activities have different pay rates, or the people have different pay rates, or both. Open your sheet again (this version). Go to sheet 1. Decide option is correct and fill in some (imaginary, if you prefer) rates per hour - for the correct option. re-post the sheet and I'll take another look at it.
Hi.
It's the activities that have different pay rates (well the staff do as well but I don't need that on this spreadsheet, I have a separate spreadsheet for inputting hours and pay when I do their wages) I just want hours on here, not pay, but I can only later work out what to pay them if I know how many hours are admin and how many are instructor hours, due to the 2 different pay rates for admin and instructor, and I don't want to be working it out manually each month.
I've re-attached the spreadsheet you sent me with the venues/times shadded a slightly lighter shade than that of the colours in the key, which'll look better for when it's printed anyway.
Thanks,
Sally
Lots of info at the link pertaining to calculations based on color formats:
http://www.cpearson.com/Excel/colors.aspx
IMHO, this should be avoided as it doesn't work the way you need it to work!
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks