Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell
a1 a2 a3 a4 a5 a6 a7 a10
100 113 145 7 65 1007 3 245 From the cells and numbers above, I need to get a formula or conditional format setting that will show me which cell A10's number will no longer be enough. Cell A10 is the number of items I have, the other cells are the number of items I need to ship each day. I need something to show me at which cell I don't have enough items on hand. The answer for the data above would be Cell A3. At cell A3 there is no longer enough in cell A10 to fill that order. In....say cell a11 I need it to show me either which cell number or date or something of when I will be out of items. Thanks, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell
This one can be handled with a nested IF statement in A11:
=IF(A1A10,"A1",IF(SUM(A1:A2)A10,"A2",IF(SUM(A1:A 3)A10,"A3",IF(SUM(A1:A4)A10,"A4",IF(SUM(A1:A5)A 10,"A5",IF(SUM(A1:A6)A10,"A6",IF(SUM(A1:A7)A10," A7","All OK"))))))) If you have more than the 7 days to consider, then a different approach will have to be taken "Jelewis72076" wrote: a1 a2 a3 a4 a5 a6 a7 a10 100 113 145 7 65 1007 3 245 From the cells and numbers above, I need to get a formula or conditional format setting that will show me which cell A10's number will no longer be enough. Cell A10 is the number of items I have, the other cells are the number of items I need to ship each day. I need something to show me at which cell I don't have enough items on hand. The answer for the data above would be Cell A3. At cell A3 there is no longer enough in cell A10 to fill that order. In....say cell a11 I need it to show me either which cell number or date or something of when I will be out of items. Thanks, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell
If you only have a small number of cells to test:
this formula returns the cell address of the amount that would exceed the limit in A10:: ="A"&(MATCH(A10,SUBTOTAL(9,OFFSET(A1,0,0,{1,2,3,4, 5,6,7})),1)+1) Otherwise, this method is more flexible: ="A"&(MATCH(A10,INDEX(SUBTOTAL(9,OFFSET(A1,0,0,COL UMN(A1:I1))),0),1)+1) Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Jelewis72076" wrote in message ... a1 a2 a3 a4 a5 a6 a7 a10 100 113 145 7 65 1007 3 245 From the cells and numbers above, I need to get a formula or conditional format setting that will show me which cell A10's number will no longer be enough. Cell A10 is the number of items I have, the other cells are the number of items I need to ship each day. I need something to show me at which cell I don't have enough items on hand. The answer for the data above would be Cell A3. At cell A3 there is no longer enough in cell A10 to fill that order. In....say cell a11 I need it to show me either which cell number or date or something of when I will be out of items. Thanks, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell
JLatham,
This works almost perfectly. It does exactly what I asked. Now I guess I need one more thing, Now that I know how to get it to reference what I want it to. Is there a way to get this formula to reference....say a date in another cell as opposed to the cell location? I changed the formula to reference a date instead of A1 - A7 but only those appear and not what is in the cell. How do I get it to do that? "JLatham" wrote: This one can be handled with a nested IF statement in A11: =IF(A1A10,"A1",IF(SUM(A1:A2)A10,"A2",IF(SUM(A1:A 3)A10,"A3",IF(SUM(A1:A4)A10,"A4",IF(SUM(A1:A5)A 10,"A5",IF(SUM(A1:A6)A10,"A6",IF(SUM(A1:A7)A10," A7","All OK"))))))) If you have more than the 7 days to consider, then a different approach will have to be taken "Jelewis72076" wrote: a1 a2 a3 a4 a5 a6 a7 a10 100 113 145 7 65 1007 3 245 From the cells and numbers above, I need to get a formula or conditional format setting that will show me which cell A10's number will no longer be enough. Cell A10 is the number of items I have, the other cells are the number of items I need to ship each day. I need something to show me at which cell I don't have enough items on hand. The answer for the data above would be Cell A3. At cell A3 there is no longer enough in cell A10 to fill that order. In....say cell a11 I need it to show me either which cell number or date or something of when I will be out of items. Thanks, |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell
If I understand you correctly, you can do it this way. Let's say your dates
are in column B on same rows with the 7 values to test. You'd need to format A11 as a date also, same as in B1:B7, then the formula in A11 becomes =IF(A1A10,B1,IF(SUM(A1:A2)A10,B2,IF(SUM(A1:A3)A 10,B3,IF(SUM(A1:A4)A10,B4,IF(SUM(A1:A5)A10,B5,IF (SUM(A1:A6)A10,B6,IF(SUM(A1:A7)A10,B7,"All OK"))))))) "Jelewis72076" wrote: JLatham, This works almost perfectly. It does exactly what I asked. Now I guess I need one more thing, Now that I know how to get it to reference what I want it to. Is there a way to get this formula to reference....say a date in another cell as opposed to the cell location? I changed the formula to reference a date instead of A1 - A7 but only those appear and not what is in the cell. How do I get it to do that? "JLatham" wrote: This one can be handled with a nested IF statement in A11: =IF(A1A10,"A1",IF(SUM(A1:A2)A10,"A2",IF(SUM(A1:A 3)A10,"A3",IF(SUM(A1:A4)A10,"A4",IF(SUM(A1:A5)A 10,"A5",IF(SUM(A1:A6)A10,"A6",IF(SUM(A1:A7)A10," A7","All OK"))))))) If you have more than the 7 days to consider, then a different approach will have to be taken "Jelewis72076" wrote: a1 a2 a3 a4 a5 a6 a7 a10 100 113 145 7 65 1007 3 245 From the cells and numbers above, I need to get a formula or conditional format setting that will show me which cell A10's number will no longer be enough. Cell A10 is the number of items I have, the other cells are the number of items I need to ship each day. I need something to show me at which cell I don't have enough items on hand. The answer for the data above would be Cell A3. At cell A3 there is no longer enough in cell A10 to fill that order. In....say cell a11 I need it to show me either which cell number or date or something of when I will be out of items. Thanks, |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell
Absolutely perfect sir. I appreciate the help and hopefully you get paid
lots of money for the assistance you give on here. Thanks, James "JLatham" wrote: If I understand you correctly, you can do it this way. Let's say your dates are in column B on same rows with the 7 values to test. You'd need to format A11 as a date also, same as in B1:B7, then the formula in A11 becomes =IF(A1A10,B1,IF(SUM(A1:A2)A10,B2,IF(SUM(A1:A3)A 10,B3,IF(SUM(A1:A4)A10,B4,IF(SUM(A1:A5)A10,B5,IF (SUM(A1:A6)A10,B6,IF(SUM(A1:A7)A10,B7,"All OK"))))))) "Jelewis72076" wrote: JLatham, This works almost perfectly. It does exactly what I asked. Now I guess I need one more thing, Now that I know how to get it to reference what I want it to. Is there a way to get this formula to reference....say a date in another cell as opposed to the cell location? I changed the formula to reference a date instead of A1 - A7 but only those appear and not what is in the cell. How do I get it to do that? "JLatham" wrote: This one can be handled with a nested IF statement in A11: =IF(A1A10,"A1",IF(SUM(A1:A2)A10,"A2",IF(SUM(A1:A 3)A10,"A3",IF(SUM(A1:A4)A10,"A4",IF(SUM(A1:A5)A 10,"A5",IF(SUM(A1:A6)A10,"A6",IF(SUM(A1:A7)A10," A7","All OK"))))))) If you have more than the 7 days to consider, then a different approach will have to be taken "Jelewis72076" wrote: a1 a2 a3 a4 a5 a6 a7 a10 100 113 145 7 65 1007 3 245 From the cells and numbers above, I need to get a formula or conditional format setting that will show me which cell A10's number will no longer be enough. Cell A10 is the number of items I have, the other cells are the number of items I need to ship each day. I need something to show me at which cell I don't have enough items on hand. The answer for the data above would be Cell A3. At cell A3 there is no longer enough in cell A10 to fill that order. In....say cell a11 I need it to show me either which cell number or date or something of when I will be out of items. Thanks, |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell
We're all just glad that we could help come up with a solution for you. We
do it for the thanks, not for the money (there ain't any :)). So you've paid well for the help you received. "Jelewis72076" wrote: Absolutely perfect sir. I appreciate the help and hopefully you get paid lots of money for the assistance you give on here. Thanks, James "JLatham" wrote: If I understand you correctly, you can do it this way. Let's say your dates are in column B on same rows with the 7 values to test. You'd need to format A11 as a date also, same as in B1:B7, then the formula in A11 becomes =IF(A1A10,B1,IF(SUM(A1:A2)A10,B2,IF(SUM(A1:A3)A 10,B3,IF(SUM(A1:A4)A10,B4,IF(SUM(A1:A5)A10,B5,IF (SUM(A1:A6)A10,B6,IF(SUM(A1:A7)A10,B7,"All OK"))))))) "Jelewis72076" wrote: JLatham, This works almost perfectly. It does exactly what I asked. Now I guess I need one more thing, Now that I know how to get it to reference what I want it to. Is there a way to get this formula to reference....say a date in another cell as opposed to the cell location? I changed the formula to reference a date instead of A1 - A7 but only those appear and not what is in the cell. How do I get it to do that? "JLatham" wrote: This one can be handled with a nested IF statement in A11: =IF(A1A10,"A1",IF(SUM(A1:A2)A10,"A2",IF(SUM(A1:A 3)A10,"A3",IF(SUM(A1:A4)A10,"A4",IF(SUM(A1:A5)A 10,"A5",IF(SUM(A1:A6)A10,"A6",IF(SUM(A1:A7)A10," A7","All OK"))))))) If you have more than the 7 days to consider, then a different approach will have to be taken "Jelewis72076" wrote: a1 a2 a3 a4 a5 a6 a7 a10 100 113 145 7 65 1007 3 245 From the cells and numbers above, I need to get a formula or conditional format setting that will show me which cell A10's number will no longer be enough. Cell A10 is the number of items I have, the other cells are the number of items I need to ship each day. I need something to show me at which cell I don't have enough items on hand. The answer for the data above would be Cell A3. At cell A3 there is no longer enough in cell A10 to fill that order. In....say cell a11 I need it to show me either which cell number or date or something of when I will be out of items. Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I copy a value from a cell and paste it into another cell while adding it to the previous value in that cell | Excel Worksheet Functions | |||
How can I make a blank cell in a formula cell with a range of cell | Excel Discussion (Misc queries) | |||
cell data not validated if navigating cell to cell with mouse | Excel Worksheet Functions | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) |