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

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

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

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

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