Cell

[Jelewis72076]

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,

 

[JLatham]

This one can be handled with a nested IF statement in A11:

=IF(A1>A10,"A1",IF(SUM(A1:A2)>A10,"A2",IF(SUM(A1:A3)>A10,"A3",IF(SUM(A1:A4)>A10,"A4",IF(SUM(A1:A5)>A10,"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

 

[Ron Coderre]

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,COLUMN(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]

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]

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(A1>A10,B1,IF(SUM(A1:A2)>A10,B2,IF(SUM(A1:A3)>A10,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]

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]

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.