INDEX with accumulative SUM

[Mecha_Trueno]

Hi guys,

Bit stuck here.
In the example attached, I want column E to show the store number (row 3) up to which stocks are sufficient (if we accumulate the individual usage from left to right).
So in the example, I'd like it to return "store 4" for product 1.

Is there any way I can do this in a single formula? I was trying a few variants of SUM and INDEX (array entered), or am I completely thinking along the wrong lines?

Any help appreciated, thanks.

[zbor]

On Sheet2 I've add a formula.
You need to extend it to right and down as much as you have your data (or more).
Then you can use formula in Sheet1.

(Hint: You can hide Sheet2 if you like)

[Mecha_Trueno]

Thanks for the quick reply. I can't seem to download the attachment, it keeps taking me to the log in screen (even though I'm alrady logged in).

[zbor]

Don't know why...

Write this in sheet2 G4 (same cell as data in sheet1).

Please Login or Register  to view this content.

And in Sheet1 E4 enter:

Please Login or Register  to view this content.

You should get number 4.

(Replace ; with , if needed)

[Mecha_Trueno]

Don't know why...

Write this in sheet2 G4 (same cell as data in sheet1).

Please Login or Register  to view this content.

And in Sheet1 E4 enter:

Please Login or Register  to view this content.

You should get number 4.

(Replace ; with , if needed)

Yeah, that's the way went with the other day because I needed to get it done in a hurry. But I was hoping there was a more direct way, without having to rely on parallel data on another sheet.

[benishiryo]

hi Mecha_Trueno. try this at E4:
=LOOKUP(2,1/((SUBTOTAL(9,OFFSET(G4,,,,COLUMN(G4:AJ4)-COLUMN(G4)+1))<=D4)*(G4:AJ4<>"")),$G$3:$AJ$3)

[arlu1201]

Mecha,

You should be able to download the attachment now. Please try and let me know.

[Mecha_Trueno]

hi Mecha_Trueno. try this at E4:
=LOOKUP(2,1/((SUBTOTAL(9,OFFSET(G4,,,,COLUMN(G4:AJ4)-COLUMN(G4)+1))<=D4)*(G4:AJ4<>"")),$G$3:$AJ$3)

Brilliant, works perfect.
Just wondering, would it have worked just as well without the <>”” test?

Mecha,

You should be able to download the attachment now. Please try and let me know.

Nope, still getting redirected.

[benishiryo]

you're very welcome. in a scenario where total amount in G:AJ is greater than column D, it will show as "Store 30". so no. it would not work without that portion of it

[Mecha_Trueno]

Thanks for all your help guys.