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

nadia p

New Member
Joined
Jul 16, 2019
Messages
8
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?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 0
[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.
 
Upvote 0
Re: [Solved]: ARRAY number of rows and colums, how to know the correct values for the size...

That's good to hear, thanks.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top