Using a value in a cell as a max range for a sum

[sbozicevic]

Hello!

I am trying to use the value of a cell to define my max range for other functions in other cells. I am working with a huge data set, and I want to optimize my processes.

Here is what I'm trying to do.

I have a cell which represents orders: H2
This cell counts the number of orders in my worksheet, let's say H2 = 100.

Since in practice, the actual number of orders is in the hundreds of thousands, I want to use the value of H2 to set the max range for future processes.


For example;
I have a large number of cells that reference the same worksheet, so it slows down performance to have each cell go to 1,000,000. I'd like to have them know to stop at the value of H2.
=SUM(IF($A5=raw!$O$2:$O$1000000,raw!$F$2:$F$1000000-raw!$E$2:$E$1000000,0))

So I'm trying to achieve:
=SUM(IF($A5=raw!$O$2:$O$[VALUE OF H2],raw!$F$2:$F$[VALUE OF H2]-raw!$E$2:$E$[VALUE OF H2],0),0)

I'd use an eval() function in other languages to create a meaning max cell range, but I'm lost how to do this in an excel function.

Can anyone give me some advice?


thanks!!

 

[Jonmo1]

Not tested, but I think this will work...

=SUM(IF($A5=raw!$O$2:INDEX(raw!O:O,H2),raw!$F$2:INDEX(raw!F:F,H2)-raw!$E$2:INDEX(raw!E:E,H2),0),0)

Hope This Helps...

 

[sbozicevic]

Nice!

You people are more useful than soap in a shower!

 

[Jonmo1]

You people are more useful than soap in a shower!

Remind me not to have you over for dinner... PE-EW !!!

 

[sbozicevic]

Ha!

I promise not to try to use you in the shower instead of soap... =/