# Can Excel dynamically plug into a formula the last row (rows are added every week)

#### ExcelLoverMan

##### New Member
I have a spreadsheet where one or two new rows are added every week.
When I summarize my report I pick from row 2 to row n (last row value).
The report has about 30 cells and currently I have to retype the last row number 30 times)
I wanted to see if I can code something so that my report is able to pick the last row automatically
as I add a row at the bottom.
I appreciate some detailed reply on how to accomplish that.
Thank you very much!
I love Mr. Excel!

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### Canapone

##### Active Member
Hi,

one approach could be

=SUM(A2:INDEX(A2:A50,COUNT(A2:A50)))

Hope it's of some help

Last edited:

#### Canapone

##### Active Member
Hi again,

if your database would have empty rows among numbers, in order to intercept position of last number in A2:A50

Code:
``=IF(A2,SUM(A2:INDEX(A2:A50,LOOKUP(2,1/ISNUMBER(A2:A50),ROW(2:50)))),0)``

Just for sharing other ideas.

Regards

Last edited:

#### MARK858

##### MrExcel MVP
@ExcelLoverMan, can you not convert your data to a table then you can add data and it will automatically account for the number of rows for basic functions like Sum, Average, Count, Max, Min etc. ?

#### ExcelLoverMan

##### New Member
Hi folks:
Thanks for trying but I DO NOT NEED TO ADD: Here is one of the report's function:
=COUNTIFS(\$B\$2:\$B\$207,\$A\$260,\$C\$2:\$C\$207,\$C\$269,\$I\$2:\$I\$207,\$C\$262)
WHAT I WANT IS TO HAVE THE "207" that appears 3 times stored in some cell and I want it to update when I enter a new row. In this
case the last row is 207. When I went a new row I want this formula to update to 208 so I won't have to physically go into it (30 times for the whole
report).

#### MARK858

##### MrExcel MVP
Thanks for trying but I DO NOT NEED TO ADD

That is what happens when people need to guess because you haven't given the necessary info (please don't shout using capitals when it is info that you haven't previously given, its irritating).

Assuming that your data is numbers (again you haven't stated what your data actually is), then try the formula below...

=COUNTIFS(\$B\$2:INDEX(B:B,MATCH(9.99999999999999E+307,B:B)),\$A\$260,\$C\$2:INDEX(C:C,MATCH(9.99999999999999E+307,C:C)),\$C\$269,\$I\$2:INDEX(I:I,MATCH(9.99999999999999E+307,I:I)),\$C\$262)

and no there shouldn't be a space between the 6 and the 9 in 269, that is how the boards software is making it display.

If you just want the row number in a different cell, then based on column B (and assuming your cells contain numbers again)...

=ROW(INDEX(B:B,MATCH(9.99999999999999E+307,B:B)))

Last edited:

Replies
3
Views
282
Replies
2
Views
64
Replies
15
Views
450
Replies
2
Views
207
Replies
1
Views
144

1,191,307
Messages
5,985,898
Members
439,986
Latest member
DaveTee

### 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.

### Which adblocker are you using?

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

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