help with formula

  • Thread starter Thread starter stevebicks
  • Start date Start date
S

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
 
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
 
Thanks a lot for that, first impressions are that it should be perfec
for what i need :D


Stev
 
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
 
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
 
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
 
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
 
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
 
Back
Top