Help with a multiple If statement Please

[sipa]

I breed Honeybee Queens and am using excel to help manage that production against sales.
I need to know how many mature queens are available on any given order/ day against sales and which cell builder each order is allocated to by date.

So Sheet Queen orders cell c3 = date of order, d3 = quantity, e3 = date queen is available from cell builder, I1 = outstanding orders total.
and Sheet Queen Stock Cell c2 = Cell builder 1,c11 number of queens mated & available, c12 = date queens are available. Cell builders run from 1 to 8 on this sheet.

I have the following formula which almost works, and I'm sure is most inelegant. could you suggest a better way ? or at least help me to what I have working properly, many thanks.

Please Login or Register  to view this content.

[aganesan99]

Hi
can you attach a sample workbook so to quickly solve your query.

Thanks
aganesan99

[sipa]

Hi, as requested sample workbook ( Data Removed)

thanks for taking a look.
Regards

[AlKey]

Hi sipa, I am just curious why in the world do you have these blank columns (D,F, H and so on) on 'Queen Stock' sheet? All it does is complicates the whole thing.

Just one piece of your formula ",IF($I$1>'Queen Stock'!$C$11+'Queen Stock'!$E$11+'Queen Stock'!$G$11+'Queen Stock'!$I$11+'Queen Stock'!$K$11+'Queen Stock'!$M$11+'Queen Stock'!$O$11+'Queen Stock'!$Q$11,"No Avail Stock","

could be replaced with:

IF($I$1>SUM('Queen Stock'!$C$11:$Q$11),"No Avail Stock")

Your formula for Days in B column could be simplified to:

=IF(D3="","",TEXT(C3,"ddd"))

[sipa]

Thanks for the reply, and the shortened code. I've now put that into my formula.

=IF(H98=D98," ",IF($I$1<'Queen Stock'!$C$11,'Queen Stock'!$C$12,IF($I$1<=SUM('Queen Stock'!$C$11:$E$11),'Queen Stock'!$E$12,IF($I$1<=SUM('Queen Stock'!$C$11:$G$11),'Queen Stock'!$G$12,IF($I$1<=SUM('Queen Stock'!$C$11:$I$11),'Queen Stock'!$I$12,IF($I$1<=SUM('Queen Stock'!$C$11:$K$11),'Queen Stock'!$K$12,IF($I$1<=SUM('Queen Stock'!$C$11:$M$11),'Queen Stock'!$M$12,IF($I$1<=SUM('Queen Stock'!$C$11:$O$11),'Queen Stock'!$O$12,IF($I$1<=SUM('Queen Stock'!$C$11:$Q$11),'Queen Stock'!$Q$12,"No Avail Stock")))))))))

This works fine apart from, the fact it changes every cell in the column.
When adding a new order (a new row)I need the date the formula produces to stick in that cell, and not change all the others in the column.

How would I achieve that ?

[newdoverman]

The formula in Column B of Queen Orders can be replaced with:

Please Login or Register  to view this content.

Format column B as ddd

[sipa]

Just wondering if anyone can help out.
This works fine now, apart from, the fact it changes every cell in the column when adding a new order (a new row) I need the date the formula produces to stick in that cell, ie Queen orders cell, e3 = date queen is available from cell builder and not change all the others in the column.

How would I achieve that ?