Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding something to a complicated formula
Hey peeps! I have a formula that turns the cell red if the number of received items is less than the number ordered, and only if the scheduled date they were to come in is past today's date. I would like to add in the formula the ability to recognize in the ordered and recieved column if I needed to order more at a later date, or if I received the total items at different times...like 11+3 in the ordered column, meaning I ordered 11, realized I needed more and ordered 3 more. Can I make a formula to recognize this additional requirement? Here is the original formula I have in the cells: =AND(E3<D3,TODAY()G3). Thanks. -- Roz ------------------------------------------------------------------------ Roz's Profile: http://www.excelforum.com/member.php...o&userid=29746 View this thread: http://www.excelforum.com/showthread...hreadid=494894 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding something to a complicated formula
I bet you could some fancy/smancy stuff with a userdefined function, but I think
I'd take the easy route and add another column: Call it Split Shipment and put a Y if it's split. Then you could just add that check to your formula. Roz wrote: Hey peeps! I have a formula that turns the cell red if the number of received items is less than the number ordered, and only if the scheduled date they were to come in is past today's date. I would like to add in the formula the ability to recognize in the ordered and recieved column if I needed to order more at a later date, or if I received the total items at different times...like 11+3 in the ordered column, meaning I ordered 11, realized I needed more and ordered 3 more. Can I make a formula to recognize this additional requirement? Here is the original formula I have in the cells: =AND(E3<D3,TODAY()G3). Thanks. -- Roz ------------------------------------------------------------------------ Roz's Profile: http://www.excelforum.com/member.php...o&userid=29746 View this thread: http://www.excelforum.com/showthread...hreadid=494894 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding something to a complicated formula
Thanks, but unfortunately, I might need that fancy/smancy function. Sometimes the shipment comes in 6 different orders; ie a sum of 20 ordered parts comes in 5, then 4, then 3, then 5 more, etc. Do you know of any way to modify my original equation? Thanks. -- Roz ------------------------------------------------------------------------ Roz's Profile: http://www.excelforum.com/member.php...o&userid=29746 View this thread: http://www.excelforum.com/showthread...hreadid=494894 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding something to a complicated formula
I don't think you have given enough information for me to solve this problem,
but let me suggest an approach: You must have a column (A?) with the name of something you ordered (ex: pens) You must have a date ordered column and a date recieved (or to recieve) column (G) column D is the number of items ordered column E is the number of items recieved Now, considering your formula, =AND(E3<D3,TODAY()G3). It is E3 and D3 that must be changed. E3 is not one number, but rather, it should be the total of every number in column E where you recieved pens between today and the original order date. Also, D3 is not one number, but rather, it should be the total of every number in column D where you ordered pens between today and the original order date. I believe you could solve this problem by using conditional statements an hlookup(), but I don't want to go any further unless my analysis is correct. please confirm, sending some sample data if possible. -- Anne Murray "Roz" wrote: Hey peeps! I have a formula that turns the cell red if the number of received items is less than the number ordered, and only if the scheduled date they were to come in is past today's date. I would like to add in the formula the ability to recognize in the ordered and recieved column if I needed to order more at a later date, or if I received the total items at different times...like 11+3 in the ordered column, meaning I ordered 11, realized I needed more and ordered 3 more. Can I make a formula to recognize this additional requirement? Here is the original formula I have in the cells: =AND(E3<D3,TODAY()G3). Thanks. -- Roz ------------------------------------------------------------------------ Roz's Profile: http://www.excelforum.com/member.php...o&userid=29746 View this thread: http://www.excelforum.com/showthread...hreadid=494894 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding something to a complicated formula
You can create a userdefined function that returns true or false if the cell
contains a formula: Option Explicit Function HasFormula(rng As Range) As Boolean Set rng = rng.Cells(1) HasFormula = rng.HasFormula End Function Then you can include that test in your formula: =hasformula(a1) But if you start entering 5 as =5, then this won't work. It actually looks for any old formula. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Roz wrote: Thanks, but unfortunately, I might need that fancy/smancy function. Sometimes the shipment comes in 6 different orders; ie a sum of 20 ordered parts comes in 5, then 4, then 3, then 5 more, etc. Do you know of any way to modify my original equation? Thanks. -- Roz ------------------------------------------------------------------------ Roz's Profile: http://www.excelforum.com/member.php...o&userid=29746 View this thread: http://www.excelforum.com/showthread...hreadid=494894 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding something to a complicated formula
Hi Anne. I'll explain in depth. I have 3 columns that pertain to this formula: =AND(E3<D3,TODAY()G3). D3 is the number of items ordered. E3 is the number of items received. G3 is the date that these items are scheduled to come in. This formula works as it needs to , telling me in red if the number of received items (E3) is less than the number ordered (D3), ONLY if this happens past the scheduled date (G3), and that scheduled date (G3) is past today's date. In addition to this function, I also need this formula to allow for items that are delivered or ordered at different times. For example, I need 4 pens, but I received them in two different shipments, 3 in one shipment, and 1 in another shipment. I need to see this in excel so I know they came in different shipments. But excel won't recognize 3+1 in the number received column (E3) and work out the formula so that it does what my original formula does. I need to rewrite the formula so that not only does it do =AND(E3<D3,TODAY()G3), but that the cell has the ability to recognize 3+1 if shipments come separately. BTW, these all happen in individual cells, each row has something different ordered, so the formula changes from E3 and D3 all the way thru E20 and D20. So it is not about totalling at the bottom of the columns...Make sense. I hope so. If anyone can let me know if this is possible...GREAT! Thanks. -- Roz ------------------------------------------------------------------------ Roz's Profile: http://www.excelforum.com/member.php...o&userid=29746 View this thread: http://www.excelforum.com/showthread...hreadid=494894 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding something to a complicated formula
Try this:
(first assuming that you have a column (C) containing the items you ordered, like "pens") In your formula, =AND(E3<D3,TODAY()G3) Replace E3 with: (SUMIF(C3:C$20,C3,E3:E$20)) Replace D3 with: (SUMIF(C3:C$20,C3,D3:D$20)) Where 20 is the number of rows in your sheet. This will allow your function to evaluate all of the ordered and recieved pens beyond the specified date (whatever row you are in). Probable bug: If you ordered 20 pens, recieved 5, and then ordered 15 more, recieving all 15, then this function would still return red, even though you have 20 pens, because it would have your number ordered calculated as 35 pens. Therefore, you may need to keep D3 as is. Sorry if this is a bit incoherent, I'm still not sure exactly what you need. Try the function and tell me what you think. -- Anne Murray "Roz" wrote: Hi Anne. I'll explain in depth. I have 3 columns that pertain to this formula: =AND(E3<D3,TODAY()G3). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Complicated formula | Excel Worksheet Functions | |||
Formula for adding several worksheet totals | Excel Discussion (Misc queries) | |||
Complicated Formula | Excel Discussion (Misc queries) | |||
Formula help : adding | New Users to Excel |