How to insert a row into a data sheet or worksheet and update the referenced cells in the other worksheets

[vladi305]

I'm trying to pull data needed from a worksheet (Data) and update the other sheets automatically or by refreshing but what is doing is changing the absolute reference cells on the other sheets from A$1 to A$2 when a new row is inserted into Data sheet. I tried moving the data to one of the actual sheets and still changing the from 1 to 2

Im trying to avoid using Power Query

This will be the first function on I4 and it should be Data!B1 NOT Data!B2
=COUNTIF(Data!B2:F33,[@Ball])

On M4 I got this and it's changing as well
=INDEX(A:A,AGGREGATE(15,6,ROW(A$1:A$366)/(B$1:F$366=[@Ball]),1))

Thanks in advance

 

[vladi305]

Why not add the new row at the bottom of the data & then sort it by date?

Let me try that

 

[awoohaw]

Why not add the new row at the bottom of the data & then sort it by date?

I suggested a similar thing, I did not know they wanted to sort by date.

 

[vladi305]

Yes maybe that will be the only option to automate the work

That seems to work, I added the new data at the end, sorted by date and all the worksheets updated without issues. TY!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[vladi305]

Glad we could help & thanks for the feedback.

You're very welcome. It saves me hours of work weekly, that extra time will be useful to analyze the data. When I win big I'll remember the guys that helped me on this forum