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

 

[LearningEX]

=VLOOKUP(Agency_Daily_Input!D9,T9*Agency_Contract!Y9:Y10000*I9,Agency_Contract!C9:Y10)

i tried the above code and that did not work, im hoping someone can see what it is that im trying to do here

 

[mole999]

I maybe slightly off beam here
=VLOOKUP(Agency_Daily_Input!D9,T9*Agency_Contract!Y9:Y10000*I9,Agency_Contract!C9:Y10)

says vlookup in here T9*Agency_Contract!Y9:Y10000*I9 for the value Agency_Daily_Input!D9

so for example you might be searching for 100 which would work for Agency_Contract!Y9:Y10000

but when you multiply Agency_Contract!Y9:Y10000 by T9 and then by I9 I think thats where the logic actually falls down

stands back, waits to be corrected

maybe a cup of tea time and rethink what you can achieve

 

[Mackers]

Could you perhaps try to explain in writing/general terms what it is you want to accomplish with your formulas?

For example, it appears from your first formula that you are looking up to a table, and want to return a different column based on whether the day is Monday, Sunday, or Tuesday-Saturday (i.e. weekday() = 1, 7, or otherwise).

I'm not sure what your other formulas are trying to do.

Mackers

 

[LearningEX]

stands back, waits to be corrected

maybe a cup of tea time and rethink what you can achieve

lol ty sir

 

[LearningEX]

I maybe slightly off beam here
=VLOOKUP(Agency_Daily_Input!D9,T9*Agency_Contract!Y9:Y10000*I9,Agency_Contract!C9:Y10)

says vlookup in here T9*Agency_Contract!Y9:Y10000*I9 for the value Agency_Daily_Input!D9

so for example you might be searching for 100 which would work for Agency_Contract!Y9:Y10000

but when you multiply Agency_Contract!Y9:Y10000 by T9 and then by I9 I think thats where the logic actually falls down

stands back, waits to be corrected

maybe a cup of tea time and rethink what you can achieve

they are doing a similar job but using different figures and not requiring to know the days but looking up a reference 1.5 on another sheet but that 1.5 might be 1 with anther company hence the first part of the reference which in row 9 is nampak

 

[LearningEX]

i have also tried the following formula but it still returns a value in J9 and its not supposed to if J9 is less than Agency contract X9 the its supposed to display nothing and the cell I9 where this formula is located is to be blank

=IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract,22,0))

 

[mole999]

If you are testing J9, why then use a different value i.e D9

If I say that (for demonstration)
Agency_Contract!X9 = 10
J9 = 12

Then I would be
=IF(J9>Agency_Contract!X9 is TRUE so result
J9-Agency_Contract!X9 12-10 = 2

Agency_Contract!X9 = 12
J9 = 10
=IF(J9>Agency_Contract!X9 is FALSE
Then VLOOKUP(D9,Agency_Contract,22,0))
Then using D9, with column 22 of your range to get a value

does D9 = J9

 

[LearningEX]

If you are testing J9, why then use a different value i.e D9

If I say that (for demonstration)
Agency_Contract!X9 = 10
J9 = 12

Then I would be
=IF(J9>Agency_Contract!X9 is TRUE so result
J9-Agency_Contract!X9 12-10 = 2

Agency_Contract!X9 = 12
J9 = 10
=IF(J9>Agency_Contract!X9 is FALSE
Then VLOOKUP(D9,Agency_Contract,22,0))
Then using D9, with column 22 of your range to get a value

does D9 = J9

Agency contract is a database. it has information that is needed for Agency Daily Input.

i could just use the following formula

=IF(J9>8,J9-8,0)

but different contracts do things differently. so what i wanted to do was reference the contract on Agency Daily Input. and replace the >8 or 0 with >Agency_Contract!X9. which is where this information is now stored.

like ive kept saying if i enter 8 into X9 on the Agency contract sheet all works well, but if 0 is entered it returns the value in I9 but i dont want that to happen, i want it to return blank.

i really cant explain this any other way

and with regards to does D9 = J9 in a sence yes as they are on the same row and the formula is referencing this row

 

[LearningEX]

if agency Contract X9 has 8 (example) in it the do the calculation which it does,

if the X9 has 0 or is empty then do no calculations and leave the cell that has this formula in blank

 

[TheGlovner]

if agency Contract X9 has 8 (example) in it the do the calculation which it does,

if the X9 has 0 or is empty then do no calculations and leave the cell that has this formula in blank

Anybody got any VBA suggestions on how to retain the will to live?

Are you now saying that a different calculation will be used depending on the reference? That is to say the structure of calculation itself is different not the variables being passed into it?