How to reference dynamic last row of an excel table

Question

I have a function here that I'm using for a burndown chart in Excel. This is the initial burndown function below. I would like to replace row H56 with the end of my table (in case the date changes, I don't have to keep updating this value), but I can't figure out how with this complicated formula. Any help is appreciated, thanks.

=$H$2-(($H$2)/ROWS($H$2:$H$56))*(ROWS($H$2:H3)-1)

If it is really a Table, just used the structured referencing. The ranges will adjust automatically. — Ron Rosenfeld, Apr 17 '20 at 17:18
Wow, that was so easy. Thank you. I just didn't know what to google! I'll post a formal answer below — Sarah, Apr 17 '20 at 20:02

score 1 · Answer 1 · answered Apr 17 '20 at 15:19

1

Use INDEX/MATCH to return a dynamic cell reference:

 =$H$2-(($H$2)/ROWS($H$2:INDEX($H:$H,MATCH(1E+99,$H:$H))))*(ROWS($H$2:H3)-1)

answered Apr 17 '20 at 15:19

Scott Craner

148,073
10
49
81

score 0 · Answer 2 · answered Apr 17 '20 at 20:04

0

I simply replaced $H$2:$H$56 with the name of my table. In this case, it's name is Table1

=$H$2-(($H$2)/ROWS(Table1))*(ROWS($H$2:H3)-1)

Thanks Ron Rosenfeld

answered Apr 17 '20 at 20:04

Sarah

669
2
8
21

How to reference dynamic last row of an excel table

2 Answers2