Pull multiple data
 

Utilizing a similiar array formula to this one:

=IF(ISBLANK(A2),"",IF(D2<"","INFO",IF(NOT(ISNA(VL OOKUP(A2,'Stip &
Ord'!IV_D,1,0))),"IAPT",IF(NOT(ISNA(VLOOKUP(A2,Rep ay!IV_D,1,0))),"RCPO","NOIP"))))

Is there any way to set cell D2 as an option? For instance, currently by
entering the date in field D2, the result will be "INFO". What I would like
to do is continue using the initial formula, but if the result is "IAPT" or
"RCPO", then I would like to pull their effective dates from their respective
worksheets and place in D2. Is this a possibility?

if you are going o change D2 this equation should then be

=if(isblank(A2),"","INFO")

Use an appropriate formula to get the info into D2.

If you want this cell to still show the RCPO or IAPT with something in D2,
this equation would need to be changed to reflect how to deternmine what you
want with something in D2.

"luk_sr" wrote:

Utilizing a similiar array formula to this one:

=IF(ISBLANK(A2),"",IF(D2<"","INFO",IF(NOT(ISNA(VL OOKUP(A2,'Stip &
Ord'!IV_D,1,0))),"IAPT",IF(NOT(ISNA(VLOOKUP(A2,Rep ay!IV_D,1,0))),"RCPO","NOIP"))))

Is there any way to set cell D2 as an option? For instance, currently by
entering the date in field D2, the result will be "INFO". What I would like
to do is continue using the initial formula, but if the result is "IAPT" or
"RCPO", then I would like to pull their effective dates from their respective
worksheets and place in D2. Is this a possibility?

In looking at this formula:
=IF(ISBLANK(A2),"",IF(D2<"","INFO",IF(NOT(ISNA(VL OOKUP(A2,'Stip &
Ord'!IV_D,1,0))),"IAPT",IF(NOT(ISNA(VLOOKUP(A2,Rep ay!IV_D,1,0))),"RCPO","NOIP"))))

Do you think there is a simpliar way to accomplish the task? Also, if I
establish D2 as an "input" cell, would I be able to accomplish what I want
without using additional cells?


"bj" wrote:

if you are going o change D2 this equation should then be

=if(isblank(A2),"","INFO")

Use an appropriate formula to get the info into D2.

If you want this cell to still show the RCPO or IAPT with something in D2,
this equation would need to be changed to reflect how to deternmine what you
want with something in D2.

"luk_sr" wrote:

Utilizing a similiar array formula to this one:

=IF(ISBLANK(A2),"",IF(D2<"","INFO",IF(NOT(ISNA(VL OOKUP(A2,'Stip &
Ord'!IV_D,1,0))),"IAPT",IF(NOT(ISNA(VLOOKUP(A2,Rep ay!IV_D,1,0))),"RCPO","NOIP"))))

Is there any way to set cell D2 as an option? For instance, currently by
entering the date in field D2, the result will be "INFO". What I would like
to do is continue using the initial formula, but if the result is "IAPT" or
"RCPO", then I would like to pull their effective dates from their respective
worksheets and place in D2. Is this a possibility?

a slight simplification would be
=IF(A2="","",IF(D2="",IF(ISNA(VLOOKUP(A2,'Stip &
Ord'!IV_D,1,0)),IF(ISNA(VLOOKUP(A2,Repay!IV_D,1,0) ),"NOIP","RCPO),"IAPT"),"info"))

right now the values this cell can have when D2 ="" is NOIP,RCPO, or IAPT
with "INFO" for any thing other than "".

If you want to be able to have these statements even with other information
put into D2, what could be in D2 that would differenciate when this cell
should be "INFO"?

"luk_sr" wrote:

In looking at this formula:
=IF(ISBLANK(A2),"",IF(D2<"","INFO",IF(NOT(ISNA(VL OOKUP(A2,'Stip &
Ord'!IV_D,1,0))),"IAPT",IF(NOT(ISNA(VLOOKUP(A2,Rep ay!
IV_D,1,0))),"RCPO","NOIP"))))

Do you think there is a simpliar way to accomplish the task? Also, if I
establish D2 as an "input" cell, would I be able to accomplish what I want
without using additional cells?


"bj" wrote:

if you are going o change D2 this equation should then be

=if(isblank(A2),"","INFO")

Use an appropriate formula to get the info into D2.

If you want this cell to still show the RCPO or IAPT with something in D2,
this equation would need to be changed to reflect how to deternmine what you
want with something in D2.

"luk_sr" wrote:

Utilizing a similiar array formula to this one:

=IF(ISBLANK(A2),"",IF(D2<"","INFO",IF(NOT(ISNA(VL OOKUP(A2,'Stip &
Ord'!IV_D,1,0))),"IAPT",IF(NOT(ISNA(VLOOKUP(A2,Rep ay!IV_D,1,0))),"RCPO","NOIP"))))

Is there any way to set cell D2 as an option? For instance, currently by
entering the date in field D2, the result will be "INFO". What I would like
to do is continue using the initial formula, but if the result is "IAPT" or
"RCPO", then I would like to pull their effective dates from their respective
worksheets and place in D2. Is this a possibility?

The results of this array formula are stored in E2 and D2 is designated as a
date field. I guess my question would be how can I incorporate pulling the
effective dates from worksheet Stip & Ord / Repay?

"bj" wrote:

a slight simplification would be
=IF(A2="","",IF(D2="",IF(ISNA(VLOOKUP(A2,'Stip &
Ord'!IV_D,1,0)),IF(ISNA(VLOOKUP(A2,Repay!IV_D,1,0) ),"NOIP","RCPO),"IAPT"),"info"))

right now the values this cell can have when D2 ="" is NOIP,RCPO, or IAPT
with "INFO" for any thing other than "".

If you want to be able to have these statements even with other information
put into D2, what could be in D2 that would differenciate when this cell
should be "INFO"?

"luk_sr" wrote:

In looking at this formula:
=IF(ISBLANK(A2),"",IF(D2<"","INFO",IF(NOT(ISNA(VL OOKUP(A2,'Stip &
Ord'!IV_D,1,0))),"IAPT",IF(NOT(ISNA(VLOOKUP(A2,Rep ay!
IV_D,1,0))),"RCPO","NOIP"))))

Do you think there is a simpliar way to accomplish the task? Also, if I
establish D2 as an "input" cell, would I be able to accomplish what I want
without using additional cells?


"bj" wrote:

if you are going o change D2 this equation should then be

=if(isblank(A2),"","INFO")

Use an appropriate formula to get the info into D2.

If you want this cell to still show the RCPO or IAPT with something in D2,
this equation would need to be changed to reflect how to deternmine what you
want with something in D2.

"luk_sr" wrote:

Utilizing a similiar array formula to this one:

=IF(ISBLANK(A2),"",IF(D2<"","INFO",IF(NOT(ISNA(VL OOKUP(A2,'Stip &
Ord'!IV_D,1,0))),"IAPT",IF(NOT(ISNA(VLOOKUP(A2,Rep ay!IV_D,1,0))),"RCPO","NOIP"))))

Is there any way to set cell D2 as an option? For instance, currently by
entering the date in field D2, the result will be "INFO". What I would like
to do is continue using the initial formula, but if the result is "IAPT" or
"RCPO", then I would like to pull their effective dates from their respective
worksheets and place in D2. Is this a possibility?

do your named ranges IV_D on the two sheets include the date you are
interested in transfering to D2.
if they are and next to the equal to A2 value
try in D2
=IF(ISNA(VLOOKUP(A2,'Stip &
Ord'!IV_D,2,false)),IF(ISNA(VLOOKUP(A2,Repay!IV_D, 2,False),"",VLOOKUP(A2,Repay!IV_D,2,False)),VLOOKU P(A2,'Stip & Ord'!IV_D,2,false))

and in e2
=IF(A2="","",IF(ISNA(VLOOKUP(A2,'Stip &
Ord'!IV_D,1,0)),IF(ISNA(VLOOKUP(A2,Repay!IV_D,1,0) ),"NOIP","RCPO),"IAPT"))

I do not know when you would want the info statement


"luk_sr" wrote:

The results of this array formula are stored in E2 and D2 is designated as a
date field. I guess my question would be how can I incorporate pulling the
effective dates from worksheet Stip & Ord / Repay?

"bj" wrote:

a slight simplification would be
=IF(A2="","",IF(D2="",IF(ISNA(VLOOKUP(A2,'Stip &
Ord'!IV_D,1,0)),IF(ISNA(VLOOKUP(A2,Repay!IV_D,1,0) ),"NOIP","RCPO),"IAPT"),"info"))

right now the values this cell can have when D2 ="" is NOIP,RCPO, or IAPT
with "INFO" for any thing other than "".

If you want to be able to have these statements even with other information
put into D2, what could be in D2 that would differenciate when this cell
should be "INFO"?

"luk_sr" wrote:

In looking at this formula:
=IF(ISBLANK(A2),"",IF(D2<"","INFO",IF(NOT(ISNA(VL OOKUP(A2,'Stip &
Ord'!IV_D,1,0))),"IAPT",IF(NOT(ISNA(VLOOKUP(A2,Rep ay!
IV_D,1,0))),"RCPO","NOIP"))))

Do you think there is a simpliar way to accomplish the task? Also, if I
establish D2 as an "input" cell, would I be able to accomplish what I want
without using additional cells?


"bj" wrote:

if you are going o change D2 this equation should then be

=if(isblank(A2),"","INFO")

Use an appropriate formula to get the info into D2.

If you want this cell to still show the RCPO or IAPT with something in D2,
this equation would need to be changed to reflect how to deternmine what you
want with something in D2.

"luk_sr" wrote:

Utilizing a similiar array formula to this one:

=IF(ISBLANK(A2),"",IF(D2<"","INFO",IF(NOT(ISNA(VL OOKUP(A2,'Stip &
Ord'!IV_D,1,0))),"IAPT",IF(NOT(ISNA(VLOOKUP(A2,Rep ay!IV_D,1,0))),"RCPO","NOIP"))))

Is there any way to set cell D2 as an option? For instance, currently by
entering the date in field D2, the result will be "INFO". What I would like
to do is continue using the initial formula, but if the result is "IAPT" or
"RCPO", then I would like to pull their effective dates from their respective
worksheets and place in D2. Is this a possibility?

That may help, but the effective date for "INFO" is manually inputed. The
array formula would be great if this wasn't the case. I only want the
vlookup if the result of E2<"IAPT" or "RCPO". "NOIP" is the result if all
results are false. The "INFO" statements comes in when the effective date is
manually inputed. Do I need to setup something in VBA like:

With Worksheets("Sheet1").Rows()
.Cells (1,1).Value = "IV-D"
.Cells (1,2).Value = "Effective Date"
.Cells (1,3).Value = "Status"
End With

Do Until rstSheet1.EOF
IF(ISNA(VLOOKUP(A2,'Stip & Ord'!IV_D,1))) Then
Set fldSheet1 = rstSheet1.Fields (IV_D)
IV_D = fldSheet1.Value
Set fldSheet1 = rstSheet1.Fields (EffDate)
EffDate = fldSheet1.Value
Set fldSheet1 = rstSheet1.Fields (Status)
Status = fldSheet1.Value = "IAPT"
intCount = intCount + 1
Call AddToSheet (intCount, IV_D, EffDate)
rstSheet1.MoveNext
ElseIF(ISNA(VLOOKUP(A2,Repay!IV_D,1))) Then
Set fldSheet1 = rstSheet1.Fields (IV_D)
IV_D = fldSheet1.Value
Set fldSheet1 = rstSheet1.Fields (EffDate)
EffDate = fldSheet1.Value
Set fldSheet1 = rstSheet1.Fields (Status)
Status = fldSheet1.Value = "RCPO"
intCount = intCount + 1
Call AddToSheet (intCount, IV_D, EffDate)
rstSheet1.MoveNext
'Here just enter if Input$ in fldEffDate < "" Then "INFO"
ElseIF(ISNA(VLOOKUP(A2,Repay!IV_D,1,0))) Then
Set fldSheet1 = rstSheet1.Fields (IV_D)
IV_D = fldSheet1.Value
Set fldSheet1 = rstSheet1.Fields (EffDate)
EffDate = fldSheet1.Value = INPUT$
Set fldSheet1 = rstSheet1.Fields (Status)
Status = fldSheet1.Value = "INFO"
intCount = intCount + 1
Call AddToSheet (intCount, IV_D, EffDate)
rstSheet1.MoveNext
Else
Set fldSheet1 = rstSheet1.Fields (IV_D)
IV_D = fldSheet1.Value
Set fldSheet1 = rstSheet1.Fields (EffDate)
EffDate = fldSheet1.Value = ""
Set fldSheet1 = rstSheet1.Fields (Status)
Status = fldSheet1.Value = "NOIP"
intCount = intCount + 1
Call AddToSheet (intCount, IV_D, EffDate)
rstSheet1.MoveNext

Loop

With Worksheets("Sheet1").Columns("A:C")
.AutoFit
End With

Do you think this may be a better option?


"bj" wrote:

do your named ranges IV_D on the two sheets include the date you are
interested in transfering to D2.
if they are and next to the equal to A2 value
try in D2
=IF(ISNA(VLOOKUP(A2,'Stip &
Ord'!IV_D,2,false)),IF(ISNA(VLOOKUP(A2,Repay!IV_D, 2,False),"",VLOOKUP(A2,Repay!IV_D,2,False)),VLOOKU P(A2,'Stip & Ord'!IV_D,2,false))

and in e2
=IF(A2="","",IF(ISNA(VLOOKUP(A2,'Stip &
Ord'!IV_D,1,0)),IF(ISNA(VLOOKUP(A2,Repay!IV_D,1,0) ),"NOIP","RCPO),"IAPT"))

I do not know when you would want the info statement


"luk_sr" wrote:

The results of this array formula are stored in E2 and D2 is designated as a
date field. I guess my question would be how can I incorporate pulling the
effective dates from worksheet Stip & Ord / Repay?

"bj" wrote:

a slight simplification would be
=IF(A2="","",IF(D2="",IF(ISNA(VLOOKUP(A2,'Stip &
Ord'!IV_D,1,0)),IF(ISNA(VLOOKUP(A2,Repay!IV_D,1,0) ),"NOIP","RCPO),"IAPT"),"info"))

right now the values this cell can have when D2 ="" is NOIP,RCPO, or IAPT
with "INFO" for any thing other than "".

If you want to be able to have these statements even with other information
put into D2, what could be in D2 that would differenciate when this cell
should be "INFO"?

"luk_sr" wrote:

In looking at this formula:
=IF(ISBLANK(A2),"",IF(D2<"","INFO",IF(NOT(ISNA(VL OOKUP(A2,'Stip &
Ord'!IV_D,1,0))),"IAPT",IF(NOT(ISNA(VLOOKUP(A2,Rep ay!
IV_D,1,0))),"RCPO","NOIP"))))

Do you think there is a simpliar way to accomplish the task? Also, if I
establish D2 as an "input" cell, would I be able to accomplish what I want
without using additional cells?


"bj" wrote:

if you are going o change D2 this equation should then be

=if(isblank(A2),"","INFO")

Use an appropriate formula to get the info into D2.

If you want this cell to still show the RCPO or IAPT with something in D2,
this equation would need to be changed to reflect how to deternmine what you
want with something in D2.

"luk_sr" wrote:

Utilizing a similiar array formula to this one:

=IF(ISBLANK(A2),"",IF(D2<"","INFO",IF(NOT(ISNA(VL OOKUP(A2,'Stip &
Ord'!IV_D,1,0))),"IAPT",IF(NOT(ISNA(VLOOKUP(A2,Rep ay!IV_D,1,0))),"RCPO","NOIP"))))

Is there any way to set cell D2 as an option? For instance, currently by
entering the date in field D2, the result will be "INFO". What I would like
to do is continue using the initial formula, but if the result is "IAPT" or
"RCPO", then I would like to pull their effective dates from their respective
worksheets and place in D2. Is this a possibility?