Trying to prevent circular reference when automating a FIFO list

[shawn4leslee]

Background:

I have (2) individual lists on separate sheets in (1) workbook. Sheet2! has lists of (2) different types of units (2r entered in Sheet2! column G, 4r entered in Sheet2! column J) with unique text&number combo id's (i.e. abc123 in Sheet2! column H) and a date entered field for each in column prior (date for 2r is in Sheet2! column F, 4r date on column I). Multiple units and types are add daily.

Sheet1! is manual entry for requests. This sheet will have cell (Sheet1!D2) to enter the unit type.

I want to have a cell in (Sheet1!E2) to provide oldest date for that type and a cell in (Sheet1!F2) that returns the matching id (abc123).

My issue is that I don't want to that id (abc123) to be used again in reference. Any way I use to eliminate it just creates a circular reference.

What I have so far for:

Sheet1!E2
=IF(D2="","",IF(D2=2r,MIN(Sheet2!G:G,IF(D2=4r,MIN(Sheet2!J:J),"INCORRECT")))

Sheet1!F2
=IF(D2="","",IF(D2=2r,,VLOOKUP(E2,Sheet2!G:H,2,FALSE),IF(D2=4rVLOOKUP(E2Sheet2!J,2,FALSE),"")))

Advice anyone? VBA is still a little outside my comfort zone. I can use them if I have them but I just don't fully understand them (yet...)

 

[j_unsuitable]

Hey
I am not sure why any of those formulas would give you a circular reference warning, but there are a few typos:

Sheet1!E2
=IF(D2="","",IF(D2="2r",MIN(Sheet2!G:G),IF(D2="4r",MIN(Sheet2!J:J),"INCORRECT")))

Sheet1!F2
=IF(D2="","",IF(D2="2r",,VLOOKUP(E2,Sheet2!G:H,2,FALSE),IF(D2="4r",VLOOKUP(E2,Sheet2!J:K,2,FALSE),"")))

The red is stuff I added, the green comma needs to be removed.
Also check your ranges, the ones in the formulas dont quite add up to your description on top.

Does that help?

 

[shawn4leslee]

Understood. I did have some typos and I failed to mention when I get the circular warning. I get it when I try to prevent duplicates on the id#. I have tried referencing if cell Sheet1!F2 has data, I want to eliminate it from the next selection since in theory, it should be gone now.

Here are some shots of what I have:
Sheet2!


-- removed inline image ---


Formulas:

-- removed inline image ---


Sheet1!

-- removed inline image ---


D is manual entry
Formula for E:

-- removed inline image ---


Formula for F:

-- removed inline image ---

 

[shawn4leslee]

I have no idea what happened there but here are the formulas and results:

"Understood. I did have some typos and I failed to mention when I get the circular warning. I get it when I try to prevent duplicates on the id#. I have tried referencing if cell Sheet1!F2 has data, I want to eliminate it from the next selection since in theory, it should be gone now."

Here are some shots of what I have:
Sheet2!

[TABLE="width: 421"]
<tbody>[TR]
[TD]ID#[/TD]
[TD]Date in[/TD]
[TD]Type[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]2r[/TD]
[TD]Date[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]abc123[/TD]
[TD="align: right"]9-Mar-16[/TD]
[TD]2r[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9-Mar-16[/TD]
[TD]abc123[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ool603[/TD]
[TD="align: right"]15-Mar-16[/TD]
[TD]2r[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15-Mar-16[/TD]
[TD]ool603[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fsc396[/TD]
[TD="align: right"]6-Mar-16[/TD]
[TD]4r[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6-Mar-16[/TD]
[TD]fsc396[/TD]
[/TR]
[TR]
[TD]hjc652[/TD]
[TD="align: right"]21-Mar-16[/TD]
[TD]2r[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21-Mar-16[/TD]
[TD]hjc652[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]hlb201[/TD]
[TD="align: right"]1-Feb-16[/TD]
[TD]4r[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1-Feb-16[/TD]
[TD]hlb201[/TD]
[/TR]
[TR]
[TD]hdm649[/TD]
[TD="align: right"]9-Jan-16[/TD]
[TD]2r[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9-Jan-16[/TD]
[TD]hdm649[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]thg702[/TD]
[TD="align: right"]9-Mar-16[/TD]
[TD]2r[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9-Mar-16[/TD]
[TD]thg702[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
Columns G-J
[TABLE="width: 476"]
<tbody>[TR]
[TD]Date[/TD]
[TD]2r[/TD]
[TD]Date[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]=IF(C2="2r",B2,"")[/TD]
[TD]=IF(C2="2r",A2,"")[/TD]
[TD]=IF(C2="4r",B2,"")[/TD]
[TD]=IF(C2="4r",A2,"")[/TD]
[/TR]
[TR]
[TD]=IF(C3="2r",B3,"")[/TD]
[TD]=IF(C3="2r",A3,"")[/TD]
[TD]=IF(C3="4r",B3,"")[/TD]
[TD]=IF(C3="4r",A3,"")[/TD]
[/TR]
[TR]
[TD]=IF(C4="2r",B4,"")[/TD]
[TD]=IF(C4="2r",A4,"")[/TD]
[TD]=IF(C4="4r",B4,"")[/TD]
[TD]=IF(C4="4r",A4,"")[/TD]
[/TR]
[TR]
[TD]=IF(C5="2r",B5,"")[/TD]
[TD]=IF(C5="2r",A5,"")[/TD]
[TD]=IF(C5="4r",B5,"")[/TD]
[TD]=IF(C5="4r",A5,"")[/TD]
[/TR]
[TR]
[TD]=IF(C6="2r",B6,"")[/TD]
[TD]=IF(C6="2r",A6,"")[/TD]
[TD]=IF(C6="4r",B6,"")[/TD]
[TD]=IF(C6="4r",A6,"")[/TD]
[/TR]
[TR]
[TD]=IF(C7="2r",B7,"")[/TD]
[TD]=IF(C7="2r",A7,"")[/TD]
[TD]=IF(C7="4r",B7,"")[/TD]
[TD]=IF(C7="4r",A7,"")[/TD]
[/TR]
[TR]
[TD]=IF(C8="2r",B8,"")[/TD]
[TD]=IF(C8="2r",A8,"")[/TD]
[TD]=IF(C8="4r",B8,"")[/TD]
[TD]=IF(C8="4r",A8,"")[/TD]
[/TR]
</tbody>[/TABLE]


Sheet1!
[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl66, width: 64"][TABLE="width: 199"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Date[/TD]
[TD]ID#[/TD]
[/TR]
[TR]
[TD]2r[/TD]
[TD]9-Jan-16[/TD]
[TD]hdm649[/TD]
[/TR]
[TR]
[TD]4r[/TD]
[TD]1-Feb-16[/TD]
[TD]hlb201[/TD]
[/TR]
[TR]
[TD]2r[/TD]
[TD]9-Jan-16[/TD]
[TD]hdm649[/TD]
[/TR]
[TR]
[TD]2r[/TD]
[TD]9-Jan-16[/TD]
[TD]hdm649[/TD]
[/TR]
</tbody>[/TABLE]


Formulas:
Column E
[TABLE="width: 548"]
<tbody>[TR]
[TD]Date[/TD]
[/TR]
[TR]
[TD]=IF(D2="","",IF(D2="2r",MIN(Sheet2!G:G),IF(D2="4r",MIN(Sheet2!I:I),"INCORRECT!")))[/TD]
[/TR]
[TR]
[TD]=IF(D3="","",IF(D3="2r",MIN(Sheet2!G:G),IF(D3="4r",MIN(Sheet2!I:I),"INCORRECT!")))[/TD]
[/TR]
[TR]
[TD]=IF(D4="","",IF(D4="2r",MIN(Sheet2!G:G),IF(D4="4r",MIN(Sheet2!I:I),"INCORRECT!")))[/TD]
[/TR]
[TR]
[TD]=IF(D5="","",IF(D5="2r",MIN(Sheet2!G:G),IF(D5="4r",MIN(Sheet2!I:I),"INCORRECT!")))[/TD]
[/TR]
</tbody>[/TABLE]

Column F
[TABLE="width: 686"]
<tbody>[TR]
[TD]ID#[/TD]
[/TR]
[TR]
[TD]=IF(D2="","",IF(D2="2r",VLOOKUP(E2,Sheet2!G:H,2,FALSE),IF(D2="4r",VLOOKUP(E2,Sheet2!I:J,2,FALSE),"")))[/TD]
[/TR]
[TR]
[TD]=IF(D3="","",IF(D3="2r",VLOOKUP(E3,Sheet2!G:H,2,FALSE),IF(D3="4r",VLOOKUP(E3,Sheet2!I:J,2,FALSE),"")))[/TD]
[/TR]
[TR]
[TD]=IF(D4="","",IF(D4="2r",VLOOKUP(E4,Sheet2!G:H,2,FALSE),IF(D4="4r",VLOOKUP(E4,Sheet2!I:J,2,FALSE),"")))[/TD]
[/TR]
[TR]
[TD]=IF(D5="","",IF(D5="2r",VLOOKUP(E5,Sheet2!G:H,2,FALSE),IF(D5="4r",VLOOKUP(E5,Sheet2!I:J,2,FALSE),"")))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]

 

[j_unsuitable]

So what youre trying to do is return the second smallest date when 2r or 4r is entered a second time?
Maybe try using small instead of min and adding a countif
So E2 would be:
=IF(D2="","",IF(D2="2r",small(Sheet2!G:G,countif($E$2:E2,E2)),IF(D2="4r",SMALL(Sheet2!I:I,countif($E$2:E2,E2)),"INCORRECT!")))

But you could also just filter sheet2 Column "Type" and then sort column "Date in" from lowest to highest and it will give you the same results without any formulas

 

[shawn4leslee]

I tried your recommendation and get 0-Jan-00 fro all results. Surprisingly it did not change any data in column F. Since this workbook will be used among several people for different things, it needs to be fully automated. One user will be adding the data on Sheet2! while another will be filling Sheet1! and expecting excel to auto provide the oldest available unit from the type they enter.

=IF(D2="","",IF(D2="2r",SMALL(Sheet2!G:G,COUNTIF($E$2:E2,E2)),IF(D2="4r",SMALL(Sheet2!I:I,COUNTIF($E$2:E2,E2)),"INCORRECT!")))

[TABLE="width: 149"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]ID#[/TD]
[/TR]
[TR]
[TD]0-Jan-00[/TD]
[TD]hdm649[/TD]
[/TR]
[TR]
[TD]0-Jan-00[/TD]
[TD]hlb201[/TD]
[/TR]
[TR]
[TD]0-Jan-00[/TD]
[TD]hdm649[/TD]
[/TR]
[TR]
[TD]0-Jan-00[/TD]
[TD]hdm649[/TD]
[/TR]
</tbody>[/TABLE]

 

[j_unsuitable]

Sorry, stupid mistake..the countif is counting the wrong column
Change the COUNTIF part to COUNTIF($D$D2:D2,D2)

 

[j_unsuitable]

Mah not my night..Should be: COUNTIF($D$2:D2,D2), basically just change the E to D

 

[shawn4leslee]

That makes much more sense but also presents a new problem. We will consistently have multiples of the same type on same day. This new addition still allows for that duplication as each time a 2r ships, a new 2r will replace it. I also had to add a Module that I had picked up some time ago that added a time stamp with last modified. I don't fully get the first line but it worked to eliminate the duplicates after adding what you provided. I am hoping the combination will fix my problem.

Thanks a bunch.

Public Function Lastmodified(c As Range)

Lastmodified = Now()

End Function

 

[j_unsuitable]

Glad i finally got it right ^^
The three lines of code are for a user defined function..it allows you to use =Lastmodified(A Cell in your workbook) in your workbook and displays when the cell was last changed