Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with formulas
Hi,
I've a problem with below formula. I've check a few time and it should be correct. Could someone please check and let me know if they know of any error or a simplier way. =if(and(vlookup(a2,Sheet3!A:B,2,false)<=InDate,vlo okup(a2,Sheet3!A:C,3,false)=InDate,vlookup(a2,She et3!A:D,4,false),if(and(vlookup(a2,Sheet3!A:E,5,fa lse)<=Indate,vlookup(a2,Sheet3!A:F,6,false)=InDat e,vlookup(a2,Sheet3!A:G,7,false),if(and(vlookup(a2 ,Sheet3!A:H,8,false)<=InDate,vlookup(a2,Sheet3!A:I ,9,false)=InDate,vlookup(a2,Sheet3!A:J,10,false), "")))) Thanks. Kim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with formulas
It seem you have not added a closing parenthesis for each AND
=if( and(vlookup(a2,Sheet3!A:B,2,false)<=InDate,vlookup (a2,Sheet3!A:C,3,false)=InDate),vlookup(a2,Sheet3 !A:D,4,false),if(and(vlookup(a2,Sheet3!A:E,5,false )<=Indate,vlookup(a2,Sheet3!A:F,6,false)=InDate), vlookup(a2,Sheet3!A:G,7,false),if(and(vlookup(a2,S heet3!A:H,8,false)<=InDate,vlookup(a2,Sheet3!A:I,9 ,false)=InDate),vlookup(a2,Sheet3!A:J,10,false)," "))))but then again, since the formula is no in caps, you clearly did not copyand paste from the worksheet so this could be a typo only in your email--Bernard V LiengmeMicrosoft Excel MVPhttp://people.stfx.ca/bliengmeremove caps from email"Kim" wrote in ... Hi, I've a problem with below formula. I've check a few time and it should be correct. Could someone please check and let me know if they know of anyerror or a simplier way.=if(and(vlookup(a2,Sheet3!A:B,2,false)<=InDa te,vlookup(a2,Sheet3!A:C,3,false)=InDate,vlookup( a2,Sheet3!A:D,4,false),if(and(vlookup(a2,Sheet3!A: E,5,false)<=Indate,vlookup(a2,Sheet3!A:F,6,false) =InDate,vlookup(a2,Sheet3!A:G,7,false),if(and(vloo kup(a2,Sheet3!A:H,8,false)<=InDate,vlookup(a2,Shee t3!A:I,9,false)=InDate,vlookup(a2,Sheet3!A:J,10,f alse),"")))) Thanks. Kim |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with formulas
Hi Kim
There is nothing inherently wrong with your formula. In order to make it easier to read, I might have been inclined to set up a named range InsertNameDefineName Data Refers to=Sheet3!A:I There is no need to have different ranges in your formula, the offset deals with which column you want to consider. Also, using 0 rather than false, makes it all easier to read (IMO). =if(and( vlookup(a2,data,2,0)<=InDate, vlookup(a2,data,3,0)=InDate, vlookup(a2,data,4,0), if(and( vlookup(a2,data,5,0)<=Indate, vlookup(a2,data,6,0)=InDate, vlookup(a2,data,7,0), if(and( vlookup(a2,data,8,0)<=InDate, vlookup(a2,data,9,0)=InDate, vlookup(a2,data,10,0),"")))) What is the error you are getting? Or, is it the result you are getting is not what you expect? What is the value of Indate? Is it an Excel date, or a text representation of a date? Are they all Excel dates in columns A, B, C, E, F, H and I? -- Regards Roger Govier "Kim" wrote in message ... Hi, I've a problem with below formula. I've check a few time and it should be correct. Could someone please check and let me know if they know of any error or a simplier way. =if(and(vlookup(a2,Sheet3!A:B,2,false)<=InDate,vlo okup(a2,Sheet3!A:C,3,false)=InDate,vlookup(a2,She et3!A:D,4,false),if(and(vlookup(a2,Sheet3!A:E,5,fa lse)<=Indate,vlookup(a2,Sheet3!A:F,6,false)=InDat e,vlookup(a2,Sheet3!A:G,7,false),if(and(vlookup(a2 ,Sheet3!A:H,8,false)<=InDate,vlookup(a2,Sheet3!A:I ,9,false)=InDate,vlookup(a2,Sheet3!A:J,10,false), "")))) Thanks. Kim |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with formulas
Hi Roger,
Thanks for your advice. Bernard reply had solve my problem but I'll take your advice in simplified the formula by using name range. It's just I forget to clode parenthesis for the AND formula. Thanks. Kim "Roger Govier" wrote: Hi Kim There is nothing inherently wrong with your formula. In order to make it easier to read, I might have been inclined to set up a named range InsertNameDefineName Data Refers to=Sheet3!A:I There is no need to have different ranges in your formula, the offset deals with which column you want to consider. Also, using 0 rather than false, makes it all easier to read (IMO). =if(and( vlookup(a2,data,2,0)<=InDate, vlookup(a2,data,3,0)=InDate, vlookup(a2,data,4,0), if(and( vlookup(a2,data,5,0)<=Indate, vlookup(a2,data,6,0)=InDate, vlookup(a2,data,7,0), if(and( vlookup(a2,data,8,0)<=InDate, vlookup(a2,data,9,0)=InDate, vlookup(a2,data,10,0),"")))) What is the error you are getting? Or, is it the result you are getting is not what you expect? What is the value of Indate? Is it an Excel date, or a text representation of a date? Are they all Excel dates in columns A, B, C, E, F, H and I? -- Regards Roger Govier "Kim" wrote in message ... Hi, I've a problem with below formula. I've check a few time and it should be correct. Could someone please check and let me know if they know of any error or a simplier way. =if(and(vlookup(a2,Sheet3!A:B,2,false)<=InDate,vlo okup(a2,Sheet3!A:C,3,false)=InDate,vlookup(a2,She et3!A:D,4,false),if(and(vlookup(a2,Sheet3!A:E,5,fa lse)<=Indate,vlookup(a2,Sheet3!A:F,6,false)=InDat e,vlookup(a2,Sheet3!A:G,7,false),if(and(vlookup(a2 ,Sheet3!A:H,8,false)<=InDate,vlookup(a2,Sheet3!A:I ,9,false)=InDate,vlookup(a2,Sheet3!A:J,10,false), "")))) Thanks. Kim |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with formulas
How stupid of me.
I simplified the layout of the formula to make viewing easier, and still failed to notice the missing parentheses. Fortunately for you, Bernard was wide awake!!! -- Regards Roger Govier "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi Kim There is nothing inherently wrong with your formula. In order to make it easier to read, I might have been inclined to set up a named range InsertNameDefineName Data Refers to=Sheet3!A:I There is no need to have different ranges in your formula, the offset deals with which column you want to consider. Also, using 0 rather than false, makes it all easier to read (IMO). =if(and( vlookup(a2,data,2,0)<=InDate, vlookup(a2,data,3,0)=InDate, vlookup(a2,data,4,0), if(and( vlookup(a2,data,5,0)<=Indate, vlookup(a2,data,6,0)=InDate, vlookup(a2,data,7,0), if(and( vlookup(a2,data,8,0)<=InDate, vlookup(a2,data,9,0)=InDate, vlookup(a2,data,10,0),"")))) What is the error you are getting? Or, is it the result you are getting is not what you expect? What is the value of Indate? Is it an Excel date, or a text representation of a date? Are they all Excel dates in columns A, B, C, E, F, H and I? -- Regards Roger Govier "Kim" wrote in message ... Hi, I've a problem with below formula. I've check a few time and it should be correct. Could someone please check and let me know if they know of any error or a simplier way. =if(and(vlookup(a2,Sheet3!A:B,2,false)<=InDate,vlo okup(a2,Sheet3!A:C,3,false)=InDate,vlookup(a2,She et3!A:D,4,false),if(and(vlookup(a2,Sheet3!A:E,5,fa lse)<=Indate,vlookup(a2,Sheet3!A:F,6,false)=InDat e,vlookup(a2,Sheet3!A:G,7,false),if(and(vlookup(a2 ,Sheet3!A:H,8,false)<=InDate,vlookup(a2,Sheet3!A:I ,9,false)=InDate,vlookup(a2,Sheet3!A:J,10,false), "")))) Thanks. Kim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem with formulas | Excel Discussion (Misc queries) | |||
problem with formulas (2) | Excel Discussion (Misc queries) | |||
Two Formulas, and a problem with both..................... | Excel Discussion (Misc queries) | |||
Copying formulas - problem with the sum | Excel Worksheet Functions | |||
Problem with formulas | Excel Worksheet Functions |