I posted a few days ago with a COUNTIF question which I am using as part of another formula.

I am writing a macro (i'm a beginner) to manipulate a large quantity of raw financial data from my general ledger. I am using dynamic ranges in the macro that adjust themselves (both by column and row) based on the data pulled. This is an example of the dynamic range formula I am using:

=OFFSET(INDIRECT(ADDRESS(MATCH(40400,Sheet1!$E:$E, 0),5)),0,-3,COUNTIF(Sheet1!$E:$J,40400),COUNTA(Sheet1!$4:$4) )

This works great if I only want to extend the range downward for as many rows that contain account 40400. However, I would like the range to extend down for all accounts beginning with 404 (as they roll up together). A brilliant mind offered this suggestion using SUM. When I drop in the formula provided it works just fine...though it does make my computer "think" for a bit.... is there a better way???

=OFFSET(INDIRECT(ADDRESS(MATCH(40400,Sheet1!$E:$E, 0),5)),0,-3,SUM((Sheet1!$E$4:$E$65536<>"")*((LEFT(Sheet1!$E$4:$E$65536,3)="404"))),COUNTA(Sheet1!$4:$4))

I have used 65536 as the amount of data will vary at all times. Is there a way to use some sort of open arguement (like $E:$E)?

Thanks in advance for any help offered!!!!