Formula referring to table is not updating

[Abhay]

Please see attached file for better understanding

Concern:
In column E to H I have inserted Countif formula referrring to adjancent project columns in table. However, if add new project at the end (i.e. new column) formula does update by itself.

Col. E to H is used in chart below it. Since these columns does not update chart is also not updating.

I believe that with data in Table formula are updated automatically.

Please help.

 

[NARAYANK991]

Hi Abhay ,

Check your file now , adding more data for at least two or three projects.

I have added a named range called Last_Project , and used this in your formulae in the range E3 through H14.

Narayan

 

[Abhay]

Hi Abhay ,

Check your file now , adding more data for at least two or three projects.

I have added a named range called Last_Project , and used this in your formulae in the range E3 through H14.

Narayan

Thank you Narayan for such a quick reply. This formula reply helps.

 

[Abhay]

@ Narayank

Your formula is working. Can you please explain your logic of Last_Project and using it countif formula.

Also, i could find from Google that there was other option of #This Row but when i use it there is circular reference coming up I guess since my countif formula is inside table hence it is coming up. Please tell me if I am incorrect. Thus, your formula is preferable rather than having #This Row.

Really thanks a lot to you.

 

[NARAYANK991]

Hi Abhay ,

You are right that the circular reference is because your COUNTIF function is being used within the same row to which it is referring.

The named range Last_Project is defined as follows :

=INDEX(Summary_Table,0,COLUMNS(Summary_Table))

The INDEX function when used with a ROW parameter of 0 refers to all rows in a column ; similarly , if it used with a COLUMN parameter of 0 , it will refer to all columns in a row.

Here we are referring to all rows in a particular column ; what that column is , is defined by :

COLUMNS(Summary_Table)

The COLUMNS function returns a number , which is based on what is its parameter ; suppose you write :

=COLUMNS(J4:R4)

you will see 9 , since the number of columns in the range J4:R4 is 9.

So when we say COLUMNS(Summary_Table) we will get the number of columns in the table ; without any project , this is 7 ; with one project added , it becomes 8 , when you add another project it will become 9 , and so on.

The INDEX function with a row parameter of 0 and a column parameter referring to the number of columns within the table will return the last column of that table. As you add more columns to the table , since the table definition automatically changes , so does the definition of the named range Last_Project.

Narayan

 

[Abhay]

Hi Abhay ,

You are right that the circular reference is because your COUNTIF function is being used within the same row to which it is referring.

The named range Last_Project is defined as follows :

=INDEX(Summary_Table,0,COLUMNS(Summary_Table))

The INDEX function when used with a ROW parameter of 0 refers to all rows in a column ; similarly , if it used with a COLUMN parameter of 0 , it will refer to all columns in a row.

Here we are referring to all rows in a particular column ; what that column is , is defined by :

COLUMNS(Summary_Table)

The COLUMNS function returns a number , which is based on what is its parameter ; suppose you write :

=COLUMNS(J4:R4)

you will see 9 , since the number of columns in the range J4:R4 is 9.

So when we say COLUMNS(Summary_Table) we will get the number of columns in the table ; without any project , this is 7 ; with one project added , it becomes 8 , when you add another project it will become 9 , and so on.

The INDEX function with a row parameter of 0 and a column parameter referring to the number of columns within the table will return the last column of that table. As you add more columns to the table , since the table definition automatically changes , so does the definition of the named range Last_Project.

Narayan

Thank you narayan.