Any way to have a formula automatically change input when a set of new rows added?

James Mehrtens

Hi, trying to automate a process whereby the change from week to week on a project is reflected in a graphic on a dashboard.

Right now, I have the source sheet for the data copy the rows to a separate sheet once a week. I want a formula to simply find the difference between last update and the new one. This is what will be referenced for the dashboard graphic.

However since the rows get added to the bottom, the cells I need to use in the formula keep shifting. So is there any way to have a formula shift the cells it uses every time rows are added? Or a way to have the copied rows be inserted into the same spot each week, so that the new one is in the the cells referenced and pushes the old week into the other reference cells?

If there is another way to achieve this result (without having more sheets and coordinating the copying and clearing of information) I wouldn't mind trying a different route. Thanks

Paul Newcome

You should be able to use an INDEX function to pull the data you need into another sheet. For the row number portion of the INDEX function, you would use a COUNTIFS function to output the number of rows that have been entered.