CODEWITHSUNDEEP
excelexcel-formulaexcel-tables
Duck9139
Duck9139

Reputation: 69

Table Auto-Expand Changes Formula Range

The attached image is a table in a workbook I'm working out of. It's pretty self explanatory if you read the column headers. The Week number has a simple count formula to count the number of Week Ending dates in the range from the top of the data to that specific row. i.e - for Row one of the data the count range is $B$2:B2, for row 27 of the data the range is $B$2:B28

The issue is that when a new row is added the range for the new row is correct, $B$2:B29, but the range in row 27 changes to the exact same range, hence the 2 rows with a 28 count. I can't ferret out an answer from google and as far as Excel is concerned it's formula is fine... Can anyone provide some insight on this?

The formula is =COUNT($B$2:B##) depending on what row its on for those who want to see it.

enter image description here

Upvotes: 0

Views: 775

Answers (3)

chillin
chillin

Reputation: 4486

Although this question has already been answered/solved, will leave an alternative approach in case it is of use to someone else:

=ROUNDUP((B2-$B$2)/7,0)+1

This assumes that the date in B2 is effectively "week 1".

I agree with this answer, in that it is preferable to use Excel table nomenclature where possible.

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19837

Use table references instead of cell references:

=COUNT(Table1[[#Headers],[Week Ending Date]]:[@[Week Ending Date]])

This will count from the header down to the current row.

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152555

You could use INDEX:

=COUNT($B$2:INDEX(B:B,ROW()))

The issue is in the old last row Excel thinks that you want to include the whole data set as it refers to such. So when the new row is added it adds to the formula automatically.

With the formula above it will not do that.

Upvotes: 1

Related Questions