not being a master computer whizz, i have so far only been putting in manual figures but i was wondering if there was a way for the computer to do it for me

the problems are as follows:

i have set the conditioning format of the spreadsheet to put a "H" if someone is on holiday, a "S" if they are off sick and an "X" if they are not working for any other reason

these get tallied up and give you an end of the year figure for all sorts of stuff and are updated in a different spreadsheet that i don't have access to alter but can view so that i can make things right in there, it's too complicated for me to explain but it all works

what i need to do is to make sure that if i put "H" into the spreadsheet, not only does it tally up elsewhere, it also adds 9.5 (the hours that they get paid for normally) onto some other bit of the spreadsheet, rather than tally up the holidays acrued over the months/year and then start adding them up into the totals column

firstly, is this possible?

secondly, can someone show me how to do it if the answer to question 1 is yes?

thank you

 

Dojj yes it can be done mate .....just fooked on how to explain it

 

Send me a template of what you want to mattrs1600i@hotmail.com and i'll do it tonight for you

 

ok, i'll send a little version of it

 

Yep, dead easy Looks like Mondeo Man has it covered for you though

 

You should be able to use an if() function somewhere to do what you want...

 

that's the sort of thing it gets confusing with

i can do the nice simple stuff like l;earn what does what in one cell to another and then copy it for other cells, but it's the original getin i to work that does my tits in

for example, i have no idea what "if()" means althuogh i will hazard a guess that it may me that if there is a certain letter in the ( and the ) then a certain formula will be going on, but can you do that multiple times?

conditioning format only allowes me to do it 3 times for the sheet otherwise i would have used that

 

In the cell you want the 9.5hrs to appear, I would use a function something like:

=if(or(A1="H",A1="S",A1="X"),9.5,"")

Where A1 is the cell you are testing.

The IF statement says if the following statement is true (the or statement in this case), then set the value of this cell to 9.5, otherwise set it blank.

Another way of explaining it is: If any one of the conditions in the or statement is true then the value will be 9.5, otherwise blank.

PS you need the equals at the beginning of the function so Excel will recognise it as a function.

HTH

Neil.

 

ok, i understand that

how do i apply it to a certain batch of cells then?

just put a1:a30 in and hope for the best right?

 

nope, that is a 100% fail for me

 

believe it or not i havent got excel on this laptop; otherwise i'd have given it a go for you

the help facility in excel is actually quite good, its how i learnt - give that a go. Type in "if statement" into the search top right of excel and it will give you examples

if you want to apply it to a range of cells then you simply say e.g. A2:A2000 (that means it will apply the formula to all data in cells A2 -> A2000

make sense?

Matt

 

i'm at home and i don't think i've got excell here either

 

That won't work in this case lads.

Dojj, you need the formula in each cell, so copy and paste it. Excel will update the cells the formula points to itself.

Start with just one cell first though so you know you've got the formula right.

You can do this between workbooks but it's a little more complicated. If you need to do this I can show you how though.

 

you can use countif statements too. google it for more info