Formula references changing when adding a row to a table.

[drewberts]

I have a table set up on a sheet called "Input" so that data comes in automatically from a form app via zapier - this works well. One row is one set of form data and represents one month work of info and is auto added by creating a new row in the table automatically.

In a separate sheet called "Data" I pull that data into a table which is laid out as columns (one row in the input sheet is one column in the data sheet). Each column is a month and I have the year set out in advance (so 12 columns laid out) and each column references back to the row in question e.g. column A has all the data from row A in the input sheet.

The problem is when a new row gets added to the input sheet the next blank column formula references (in the data sheet) all change by one row value and don't reference the correct row anymore in the input sheet. I know and understand why it does this but that's not what I want to achieve as I have to manually change the formulas each month and I'm looking for the info to be automatic.

Both sets of data need to be tables as the info is used elsewhere in charts etc.

I guess the question I'm asking is how to get around this problem so that the references stay correct - I hope I've explained that so it makes sense.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.