Expanding a formula as rows are added to a table.

Occasional Visitor

Hi all, 

 

I am struggling to come to a solution for the following problem:

 

I have a Microsoft Form outputting its responses into a table in Excel, all nice and normal. I have inserted a new Column A which contains a formula to pull out just the Month and Year of each table row/form submission. As a result, the Forms linked table now starts in Column B. My issue is that when a new form response is received, it appears to be added to the table by adding a new row. By doing this, it is forcing the formula that is in column A down a row also, and leaving column A of the new row blank & requiring manual addition of the formula.

 

Is there a way I can make it so the formula is automatically generated into the new row created with every Form response, despite column A no longer being part of the table? I hope the above makes sense - I will find the time to reproduce the issue on a non sensitive file if not.

 

Thanks in advance for any replies.

1 Reply

@Mattw924 

I'd suggest to add to the table returned by Forms another table column(s) (to the right) with any formulas you need. If you use structured references, formulas will be automatically added to each new row, as for any other table,

 

You may add new table column as very first one, but in this case you most probably will lost formatting for the data returned by Forms.