Getting date based on conditions.

[kolonel]

A guru site should be the right place to ask.

What the ultimate goal for this sheet is to be able to put the dates from the next available purchase orders that will cover the amount required by customer, based on amount coming in, and when.

The dynamic values that can change are the following :

Date the stock purchase order is due (A11-A15)
The amount that is coming in on that purchase order (E11-E15)
The quantity ordered from the supplier (E3-E6) or remaining (diff between Qty and Rec)

I was hoping to fill the dates (J3-J6) based on the numbers in the (Remain) H column, and once the number was greater than the sales order, would use the corresponding purchase order date. Doesn't have to be pretty, just affective.

Hope that all made sense.

sample.xls

[tommyt61]

See if this works. It appeared you had cell references wrong in your instructions but i think i put together what you meant . You will notice that you get the word false in cell J4. based on the data in your sheet it says it should be 5-Sep, but based on the logic you provided it does work out. Step through that and see what you think. Maybe i am not Cyphering your instructions correctly.

[tommyt61]

Here is xls version if you require it.

[tommyt61]

I went back and looked at the sheet and i think i now correctly see how you want the logic to work. Take a look at this file and see if this is correct.

[kolonel]

I went back and looked at the sheet and i think i now correctly see how you want the logic to work. Take a look at this file and see if this is correct.

You sir, are a star.

Had a look over the formula, and its exactly the right layout. I can also see how i can adjust it if there are more dates as well.

The only thing i am trying to get my head around is that if the number required = number supplied, it returns a false statement in the date box. I am thinking there needs to be a <= in there somewhere ?

Again, much appreciated for the help.

sample.xls

[tommyt61]

You are correct .... in the sample file you uploaded you have the formula right in J3
the <= should only be right there at the very start of the formula. =IF(H3<=H11,A11,IF(
just start at J3 and copy down to J6 and you should be good to go.


I notice one other possible issue, you will still get a false in J6 because the formula is looking for data in H16 and there is no data there. Don't know if you are ok with that , might can figure out a workaround if you need to.

[kolonel]

Thanks mate.

Looks like it seems to be good, now that i have the (=) in the right spots.

Awesome stuff, keep it up.

Rob