getting a formula to do a similar job as another

[LearningEX]

i would like the following formula (1) and (2) to work in the same way as formula (3)

formula (3)

=VLOOKUP(Agency_Daily_Input!D9,Agency_Contract!$C$9:$T$10000,IF(AND(WEEKDAY(Agency_Daily_Input!B9)>1,WEEKDAY(Agency_Daily_Input!B9)<7),13,IF(WEEKDAY(Agency_Daily_Input!B9)=7,15,IF(WEEKDAY(Agency_Daily_Input!B9)=1,17)+IF(Agency_Daily_Input!G9>0.708333333333333,1))),FALSE)

the above one return a value based on cell c9 on Agency Daily Input

id like formula (2) to do the same

=IF(Agency_Contract!X9:X100000=0,0,IF(J9>Agency_Contract!X9:X100000,J9-Agency_Contract!X9:X100000,0))

formula (3)

=T9*Agency_Contract!$Y$9:$Y$100000*I9

 

[gaz_chops]

Hey Paul, upload the file i'll take a look, i'm guessing from an earlier post you want something like below, but the bit in red needs a range!
=IF(J9<Agency_Contract!X9,"",IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract,22,False)))

Gaz

 

[LearningEX]

Hey Paul, upload the file i'll take a look, i'm guessing from an earlier post you want something like below, but the bit in red needs a range!
=IF(J9<agency_contract!x9,"",if<font color="#006107">(J9>Agency_Contract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract,22,False)))

Gaz

=IF(OR(Agency_Contract!X9=0,Agency_Contract!X9="")*1=1,"",IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract!C9:Y10000,22,1)))

This works to a degree but its not looking up the company name and returning the value in Col X for that company try it out yourself change Nampak1 to Nampak2

here is my file

https://www.dropbox.com/s/hni5spbttin260s/for the forum.xlsm?dl=0</agency_contract!x9,"",if

 

[gaz_chops]

Which cells are the formulas in?

 

[LearningEX]

Which cells are the formulas in?

=IF(OR(Agency_Contract!X9=0,Agency_Contract!X9="")*1=1,"",IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract!C9:Y10000,22,1)))

is in Col I

i need it to look up the company name like the formula is col T does

col T

=VLOOKUP(Agency_Daily_Input!D9,Agency_Contract!$C$9:$T$10000,IF(AND(WEEKDAY(Agency_Daily_Input!B9)>1,WEEKDAY(Agency_Daily_Input!B9)<7),13,IF(WEEKDAY(Agency_Daily_Input!B9)=7,15,IF(WEEKDAY(Agency_Daily_Input!B9)=1,17)+IF(Agency_Daily_Input!G9>0.708333333333333,1))),FALSE)

although the results from these two formula are different the second formula looks up the company and returns whats required with regards to that company

download the workbook and see what i mean

 

[gaz_chops]

OK found it
so am i right that what you want is to

Lookup D9 and check if Col X = 0, if it is then blank, if not then check if J9 > the same lookup, if it is then J9 - same Lookup value, otherwise the same lookup value

 

[LearningEX]

if that works then yes

if you change nampak1 to nampak2 on the Agency Daily Input sheet then something should happen

 

[gaz_chops]

OK,
1st I wouldn't do the same lookup 3 or 4 times!
In Column U (U9) enter =VLOOKUP(D9,Agency_Contract!$C$9:$Y$10,21,FALSE)
In Column I (I9) enter =IF(U9=0,"",IF(J9>U9,J9-U9,U9))

Test to see if that is what you want?

Gaz

 

[LearningEX]

OK,
1st I wouldn't do the same lookup 3 or 4 times!
In Column U (U9) enter =VLOOKUP(D9,Agency_Contract!$C$9:$Y$10,21,FALSE)
In Column I (I9) enter =IF(U9=0,"",IF(J9>U9,J9-U9,U9))

Test to see if that is what you want?

Gaz

Gaz,

on the Agency Daily Input sheet in col T i have a formula that looks up the company name and return a value based on the day of the week and what hourly pay is required for that company and date.

if i change the company name it looks up the hourly rate for that company and put it into that col T which is great

[COLOR=#333333]=VLOOKUP(Agency_Daily_Input!D9,Agency_Contract!$C$9:$T$10000,IF(AND(WEEKDAY(Agency_Daily_Input!B9)>1,WEEKDAY(Agency_Daily_Input!B9)<7),13,IF(WEEKDAY(Agency_Daily_Input!B9)=7,15,IF(WEEKDAY(Agency_Daily_Input!B9)=1,17)+IF(Agency_Daily_Input!G9>0.708333333333333,1))),FALSE)[/COLOR]

all working all dancing does as it supposed to. it looks up date and company name.

this formula below NEEDS to look up the company name and use what ever is in col X for the required company

=IF(OR(Agency_Contract!X9=0,Agency_Contract!X9="")*1=1,"",IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract!C9:Y10000,22,0)))

this formula works to a point, its not looking up the company and grabbing whats in Col X for that row (company)

if Nampak1 pays overtime after 8 hours then there will be an 8 in col X (8 could be what ever the user puts in)
if Nampak2 does NOT pay over time after 8 hours then either the relavant cell in Col X will be blank or 0

so if thats the case for example if Col X on the Agency contract sheet has 0 or blank then Col I and what ever cell will be blank
if it has an 8 in col x then formula will do the sum of col J#(daily input) - col I (agency contract) for example

if the driver did 10 hours and the company pays over time after 8 hours then going by the above formula 10 - 8 will be "2" that will be displayed in Col I on the daily input sheet

its not different to whats happening with regards to the long formula above

 

[gaz_chops]

Did you test what I suggested?

 

[LearningEX]

Did you test what I suggested?

it needs to be all in one formula you have split it in to two formulas going into two different cells