ARRAY number of rows and colums, how to know the correct values for the size...

[nadia p]

This is the first time I'm trying to use ARRAY's in Excel.

Reference:
=IF(ROWS(A$10:A10)>$D$8,"",INDEX(Gas!A$6:A$18,SMALL(IF((Gas!$A$6:$A$18>=$C$4)*(Gas!$A$6:$A$18<=$E$4),ROW(Gas!A$6:A$18)-ROW(Gas!$A$6)+1),ROWS(A$10:A10))))

For example, it seems that the reference table A6:A18 in this case contains the amount of rows with data as defined today. Later the number of rows will grow to the amount of transactions entered (i.e. A6:A100) however how does one know what the last row will be to update the formula? If I enter A6:A10000 this seems to break the formula from returning the correct values.

Can one define A6:A somehow and let Excel define A6:A18 for example?

The purpose of this in total is to create a mater report of transactions per "Utility" (i.e. Gas, Water, etc...) based on a FROM and TO dates. I found I can define everything as "static" info for today but as the number of transactions increase per Utility I then have to look up the last row entered and update the formula's.

Any suggestions?

 

[StephenCrump]

Try: =IF(ROWS(A$10:A10)>$D$8,"",INDEX(MyRange,SMALL(IF((MyRange>=$C$4)*(MyRange<=$E$4),ROW(MyRange)-ROW(INDEX(MyRange,1))+1),ROWS(A$10:A10))))

where:

MyRange:= =Gas!$A$6:INDEX(Gas!$A:$A,COUNT(Gas!$A:$A)-COUNT(Gas!$A$1:$A$6)+ROW(Gas!$A$6))

This assumes your column Gas!A6:Axx is fully populated with numeric values? If that's not correct, we'll need a different formula for MyRange.

 

[nadia p]

Try: =IF(ROWS(A$10:A10)>$D$8,"",INDEX(MyRange,SMALL(IF((MyRange>=$C$4)*(MyRange<=$E$4),ROW(MyRange)-ROW(INDEX(MyRange,1))+1),ROWS(A$10:A10))))

where:

MyRange:= =Gas!$A$6:INDEX(Gas!$A:$A,COUNT(Gas!$A:$A)-COUNT(Gas!$A$1:$A$6)+ROW(Gas!$A$6))

This assumes your column Gas!A6:Axx is fully populated with numeric values? If that's not correct, we'll need a different formula for MyRange.

Thank you, StephenCrump. Initially this string as in a Function {} and I didn't know how to define/set the variable "MyRange" that way. I've been reading up on creating a VBA macro to try this. I'm still new to these more advanced commands in Excel. There are many blank Rows in Column A. I assume Excel has a way to reference the LastField, so I'm researching that as well.

 

[StephenCrump]

There are many blank Rows in Column A. I assume Excel has a way to reference the LastField ...

If Gas!A6:Axx is numeric, you can use:

MyRange: =Gas!$A$6:INDEX(Gas!$A:$A,MATCH(9.99999999999999E+307,Gas!$A:$A))

To create MyRange, select Formula/Name Manager/New... and then type:

Name: MyRange
Refers To: =Gas!$A$6:INDEX(Gas!$A:$A,MATCH(9.99999999999999E+307,Gas!$A:$A))

 

[nadia p]

[Solved]: ARRAY number of rows and colums, how to know the correct values for the size...

I believe Axx should be a numeric value since I can't imagine that Rows will surpass 10,000 in total.

Thank you StephenCrump, you've been a real help.

 

[StephenCrump]

Re: [Solved]: ARRAY number of rows and colums, how to know the correct values for the size...

That's good to hear, thanks.