Printable View
Hi
can anyone help me with this formula please.
its just not doing what i want it to do.Code:=VLOOKUP(D9,Agency_Contract!C9:Y11,22,IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,0))
vlookup references based on a D9
IF is what i want it to do
ive even tried it like this but this one just returns the value in J9 but i dont want that to happen
Code:=IF(J10>Agency_Contract!X10,J10-Agency_Contract!X10,VLOOKUP(D10,Agency_Contract!C10:Y12,22))
So to verify what your function reads to me:
look up D9 in range c9 to y11
and display a cell 22 spaces to the right if a match is found
else
display either j9-x9 if j9 is bigger
or 0 if x9 is bigger
if I am on the right track I would put the if at the begining
not tested I'm away from my pc, but looks like it might be closerCode:if (j9 > agency_contract!x9, vlookup(d9, Agency_contract!c9:y11,22,j9-agency_contract!x9), 0)
ok so i thought id try and find where the error lays, and this is what i got so far
if Agency_Contract X10 has an 8 in it then the formula works, however if there is a 0 in the same cell then all it does it enters the figure that in the cell next to where this formula lays
which is on Agency_Daily_Input I10Code:=IF(J10>Agency_Contract!X10,J10-Agency_Contract!X10,"")
so...if J10 has 10.5 (which is number of hours done) in it and Agency_Contract!X10 has an 8 in it then the formula returns 2, which is correct, but.....
if Agency_Contract!X10 has an 0 in it then 10.5 is returned instead of a Blank cell or 0. i hope this has helped in helping me.
as i plan to add a vlookup function to this as well
i tried that but all that does is return the value of Agency_Contract X9 which is either 8 or 0Quote:
Originally Posted by mperrah
How about posting sample before and after data or upload your workbook
under post reply click go advanced and click the paperclip
https://www.dropbox.com/s/hni5spbtti...orum.xlsm?dl=0Quote:
Originally Posted by mperrah
If we know the ranges of the data source and the function to manipulate it and the destination, this can be done in VBA or formulas. Excel has some powerful tools built that can work super fast. But sometimes a little finese is required for more complex manipulating
I'll run some test first thing in the morning.
i cant put a range name in (well i dont think i cant) as Agency Contract sheet has an advanced filter on itQuote:
Originally Posted by mperrah
You seem to be chasing this one around a bit
Instead of this - per post#01:
=VLOOKUP(D9,Agency_Contract!C9:Y11,22,IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,0))
Try
=IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract,22,1))
Why?
If you translate the formula into what it is being asked to do
=VLOOKUP(D9,Agency_Contract!C9:Y11,22,IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,0))
tells Excel to
Attachment 13176
lookup cell D9[COMMA] in Agency_Contract Range C9:Y11 (ColC is the match)[COMMA] value we want is in column X [COMMA] HERE we expect a TRUE or FALSE argument BUT there is an if statement here
i tried that but it still returns a value from j9 of Agency contract X9 is 0 if X9 is 8 it works fine but not if X9 is 0Quote:
Originally Posted by Yongle
The formula would yield that result if J9 is blank
So try
=IF(J9="","XXX",IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract,22,1)))
still returns value of J9 which i dont want to happen, J9 shows hours worked, J9 will never be blank at it shows hours worked the problem is if Agency Contract X9 is 0 thats the part of the formula that has the issue. if the cell X9 on Agency contract is 8 everything works great, but if 0 is entered the thats where its going wrong.Quote:
Originally Posted by Yongle
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
So change the condition to reflect that
FROM:
=IF(J9="","XXX",IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract,22,1)))
TO:
=IF(Agency_Contract!X9=0,"XXX",IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract,22,1)))
and then alter the "XXX" to whatever you want the cell to show
Our posts just crossed.
The above will work if the cell contains a zero.
But now you have added another condition. You also want it to check if the cell is blank.
If we sort that are we done?
thank you sooooooooo much sir i really mean it.....thank youQuote:
Originally Posted by Yongle
Quote:
Originally Posted by Yongle
ok another problem now the vlookup part is not working, referencing D9 or D10 or what ever is in Col DQuote:
Originally Posted by Yongle
if the company changes then it needs to take that into account, so if i change D9 from Nampak1 which has 8 in X9 to Nampak2 which has 0 in it, then this should be picked up and and the formula change accordingly
it needs to look up the company and say oh yes X Blah is paying overtime after 8 or what ever is in Col X.
How about you attach a sample file that we can work on. At the moment we seem to chasing shadows on a dark night. In your sample file give a number of possible scenarios so that we can test any proposal
To also check for blanks try
=IF(OR(Agency_Contract!X9=0,Agency_Contract!X9="")*1=1,"XXX",IF(J9>Agency_C ontract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract,22,1)))
Explanation
The bit inside the OR statement returns a TRUE value if both conditions are met, and FALSE otherwise
To turn that into something we can use we multiply by 1 and TRUE becomes the number 1 and FALSE becomes the number 0
Quote:
Originally Posted by Yongle
i need the below code to do what the above code does with regards to looking up who the contract isCode:=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)
here it he link to my fileCode:=IF(Agency_Contract!X9=0,"",IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract!C9:Y10,22,1)))
ive highlight the cell im talking about in yellow
https://www.dropbox.com/s/hni5spbtti...orum.xlsm?dl=0
This should sort the Look up error. My apologies. I was using a named range and forgot to convert it back after testing
=IF(OR(Agency_Contract!X9=0,Agency_Contract!X9="")*1=1,"XXX",IF(J9>Agency_C ontract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract!C9:Y11,22,1)))
its not looking up the name, if i change Nampak1 to nampak2 col I does not changeQuote:
Originally Posted by Yongle
in the ....VLOOKUP(D9,Agency_Contract!C9:Y11,22,1)))
Try changing the last 1 to a 0
does not work.Quote:
Originally Posted by Yongle
are you in the uk btw
PLEASE don't use Dropbox - I don't want to join
Click [Advanced] and use the paperclip icon to attach a file
(BTW - I've always found it a bit finicky so you might have to play around with it a bit)
It is very sunny here in Cornwall today!
Now - I am puzzled. The Vlookup is as per your original formula in post#01
There are 2 things you can try next
1 Remove the 1 or 0 and the preceding comma (which sets the argument to default)
changing the formula to
...VLOOKUP(D9,Agency_Contract!C9:Y11,22)))
If that changes nothing then we must test the original Vlookup
2 Can you take one step back and verify the VLookup as a standalone function?
and confirm that it works
thanks
If you cannot make VLookup work :
Attach a workbook that only contains the 2 sheets we need:
- sheet "Agency_Contract" and if its values are dependent on other sheets please paste values over all cells in the VLookUp table (- so that we can see the actual table of values and not a bagfull of errors.)
- sheet where all this work is going to be placed
And as requested by Paul in post#27 click on advanced and the paperclip to attach the file
thanks
Yon
I pulled his file from DropBox and Deleted the Forms (17 of them)
his file was over 4mbAttachment 13181
also made all sheets visible.
the "Invoice" sheet is where the formula are.
Good Luck, there is a lot going on
***Solved**** many thanks for all your help i really mean it.
solution here
Code:=IF(J10<8,"",IF(VLOOKUP(D10,Agency_Contract!$C$9:$Y$10,22,FALSE)=0,"",IF(J10>VLOOKUP(D10,Agency_Contract!$C$9:$Y$10,22,FALSE),J10-VLOOKUP(D10,Agency_Contract!$C$9:$Y$10,22,FALSE),VLOOKUP(D10,Agency_Contract!$C$9:$Y$10,22,FALSE))))
Invoice sheet was hidden as there was not work needed on that sheet sirQuote:
Originally Posted by mperrah
@LearningEXL
I am glad that we were able to answer your questions. Can you now click on Thread Tools and mark the thread as solved
thanks
Yon