circular reference using vlookup
 

I have a spreadsheet with the following information:
Cell I6 is blank until a session has been completed, at which time the
trainer will enter the date of the session.
Cell J6 is a vlookup that will tell me the # of calls taken on the date that
cell I6 was filled in. It uses a circular reference so that the number of
calls will not change once it is entered using the vlookup.
Cell K6 is a vlookup in the same table that will remain blank until the date
is filled in for cell I6, however is will change every day to track the
current # of calls taken to date.
Cell L6 is my problem. I would like to enter something that will lookup the
current accept rate (column 9 of the same source) once the current # of calls
(K6) is 100 more than the # of calls at the session (J6). I would like for
this value to not change once it is entered (just like cell K6). I would
like for it to remain blank if k6 is not 100 more than J6.

Here are my formulas:
I6 - the trainer enters a date

J6 -
=IF(I6="","",IF(J6="",VLOOKUP(A6,'ABAY_Training_AC RF_Current_All'!$A$1:$I$218,6,TRUE),J6))

K6 -
=IF(I6="","",VLOOKUP(A6,'ABAY_Training_ACRF_Curren t_All'!$A$1:$I$218,6,TRUE))
L6 - ??? (Please help!)

circular reference using vlookup
 

How about:

=IF(I6="","",IF(K6-
J6<100,"",IF(L6="",VLOOKUP(A6,'ABAY_Training_ACRF_ Current_All'!$A$1:$I
$218,9,TRUE),L6)))

Does this work as expected?
Kostis Vezerides

On Feb 28, 6:17 pm, clarknv wrote:
I have a spreadsheet with the following information:
Cell I6 is blank until a session has been completed, at which time the
trainer will enter the date of the session.
Cell J6 is a vlookup that will tell me the # of calls taken on the date that
cell I6 was filled in. It uses a circular reference so that the number of
calls will not change once it is entered using the vlookup.
Cell K6 is a vlookup in the same table that will remain blank until the date
is filled in for cell I6, however is will change every day to track the
current # of calls taken to date.
Cell L6 is my problem. I would like to enter something that will lookup the
current accept rate (column 9 of the same source) once the current # of calls
(K6) is 100 more than the # of calls at the session (J6). I would like for
this value to not change once it is entered (just like cell K6). I would
like for it to remain blank if k6 is not 100 more than J6.

Here are my formulas:
I6 - the trainer enters a date

J6 -
=IF(I6="","",IF(J6="",VLOOKUP(A6,'ABAY_Training_AC RF_Current_All'!$A$1:$I$218,6,TRUE),J6))

K6 -
=IF(I6="","",VLOOKUP(A6,'ABAY_Training_ACRF_Curren t_All'!$A$1:$I$218,6,TRUE))
L6 - ??? (Please help!)