gggrrr more formula and vlookuo help required

[smiler44]

No matter how I try I just can not do this!
I have attached a workbook to help this time. There are 3 tabs on the workbook, source_data, vlookup and sheet1. vlookup holds the lookup table, source data contains the data that I will use to create sheet1. the data on source sheet changes. on sheet1 the text in Green represents where the formulas have worked as they should, in Red is where the formulas have not worked as required. there is one formula for column A in sheet 1 and another formula for column C. the formula in column A should always be the same except for it will increment as it moves down the rows, the same for the formula in c.
I have written next to each one in sheet1 what is wrong. you may feel I am asking a lot but I have spent hours and hours on this and can not resolve this :banghead:, so please if you can, help me.

the workbook should be a.xlsx but I can only upload an .xls

Thank you in advance
smiler44

 

[namliam]

why should you open up 100 different threads on the same subject, you know you can just stick to one?

Why would A6 and A7 show 00:00??

 

[smiler44]

namliam I thought the other thread gave the appearance of being resolved and with this thread I have added a spread sheet to help.

with the absence codes for A6 and A7 00:00 is appropriate as it helps to signify that people are not available for work. al is an all day holiday so 00:00 is good. lp is an afternoon holiday, for this I'll use the left 4 digits from the source data sheet but use a default 12:00 as the finish time.
there could be lost of absences that will use 00:00 as start and finish time but also some that will use only the start time from the source data or just the finish time from the source data, for these I want to use default times

this is very complicated and requires a lot of knowhow and patience to try and tray again to find the right resolution. namliam I will accept your offer if you want to take this on I just don have the know how

I am sure that I can not think of all senarios that will crop up and it could be what works now may not in a weeks time

dirk pitt

 

[namliam]

Dirk,

The easiest way to debug complex formula's like you are doing is to "devide and conqeur"
Take your formula's and seperate them in seperate columns.

You are wanting to return 00:00 (aka "0") from your lookup but you are preventing they from happening by your IF(0 = VLookup) check....

 

[smiler44]

namliam, thank you. this may seem strange but what does if(0=vlookup) actually mean?

i will true as you say do it bit by bit and see how i get on, piecing it together masy give me a problem but i'll try
thank you
smiler44

 

[Brianwarnock]

namliam, thank you. this may seem strange but what does if(0=vlookup) actually mean?

smiler44

it means test if the VLOOKUP returns 0, just standard if statement.

due to the demise of my trusty 12 year old desk top I am currently wresting with using a laptop, windows 8 and excel 2013 so not getting very far, I wonder if one does get too old to learn.

however a couple of observations
I don't think that you need the if(0= etc just the first VLOOKUP
some of the cell references don't seem correct
the use of vlookup for your sheet name is, in my opinion, bad practice as it makes the reading of your formulae more difficult call it mytable because that's what it is.

brian

 

[Brianwarnock]

Correction, call it tablesht and call the range that makes up the table mytable. Using a named range in this sort of situation not only makes coding simpler but any increase in the size of the range means a change in one place only not in each and every formula.

Brian

 

[namliam]

namliam, thank you. this may seem strange but what does if(0=vlookup) actually mean?

it is supposed to be a reference to your If statement in the sheet that does this check of 0=Vlookup(....)

 

[smiler44]

it means test if the VLOOKUP returns 0, just standard if statement.

due to the demise of my trusty 12 year old desk top I am currently wresting with using a laptop, windows 8 and excel 2013 so not getting very far, I wonder if one does get too old to learn.

however a couple of observations
I don't think that you need the if(0= etc just the first VLOOKUP
some of the cell references don't seem correct
the use of vlookup for your sheet name is, in my opinion, bad practice as it makes the reading of your formulae more difficult call it mytable because that's what it is.

brian

sorry to take so long to reply, I have been away on holiday and had no internet access.

Brian,
noted about using the sheet name in the formula. Once the formula is sorted I can make the change. The cell reference for vlookup should be A1 ant A2 as I have put in a few places.

smiler44