help with formula

[stevebicks]

Hi all

i'm hoping someone can help me with a formula to do the following on a
timesheet

if i type L in a cell, (L = Late shift), then the formula will evaluate
that to a number, say 7.5 in another selected cell,(7.5 would be the
hours worked)
also would it be possible for the formula to relate to as many as 10
different letters ie: E for early, T for Training Day etc

hope this is enough inf


thanks in advance for any help

Steve

 

[Peo Sjoblom]

Create a 2 column table with the letters in the leftmost column, then use
VLOOKUP

=IF(A2="","",VLOOKUP(A2,Table,2,0))

or in real it might look like

=IF(A2="","",VLOOKUP(A2,'Sheet2'!A1:B10,2,0))

where Sheet2 A1:B10 would be the table, where A1:A10 holds the letters
and the adjacent cells in B the hours and A2 is the cell where you put the
type
of letter you want to lookup

 

[Don Guillett]

You can use a worksheet_change event with select case to do that.

 

[stevebicks]

Thanks a lot for that, first impressions are that it should be perfec
for what i need


Stev

 

[Don Guillett]

glad to help. Post back if you have problems.

 

[stevebicks]

Hi again

just found a slight problem, not with the formula but with autofill

when i try autofill it changes the cell locations as it should ie: a
goes to b2 then c2 etc but it also changes the table location fro
a1:b10 if u know what i mean lol, so how can i get the autofill to kee
the table location as a constant

thanks again

Stev

 

[Peo Sjoblom]

Use

$A$1:$B$10

for the table, easiest way is to select the range in the formula bar and
press F4 to make it absolute

 

[stevebicks]

Once again thanks a million fellas, saved me hours

thanks again

Steve

 

[David McRitchie]

Hi Steve,
Another way that would generally be used for larger tables
would be with a defined name. Advantage is that is can
be used by all the worksheets in a workbook. Of course
you could also use =sheet10!$A$1:$B$10

 

[stevebicks]

Hi again all

same timesheet but need another formula

I have a cell range, say A26:G26 which represents monday to sunday, i
i type in a letter which represents type of shift worked then in cell
A1:G1 the formula previously supplied by you kind folk inserts th
hours worked, now i need a formula that if i type say S for sick i
more than one of those cells it will calculate each S to be 7.5 an
insert the total for that range into one cell, say J1

hope thats clear enough, i'm only a self taught excel newbie lol

thanks in advance for any help

Stev

 

[David McRitchie]

Hi Steve,
Use COUNTIF and multiply by 7.5

 

[stevebicks]

Thanks for the speedy reply,once again you've saved me hours (being sel
taughts ok, but god it's time consuming)

ever grateful

Stev