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

 

[awoohaw]

Hi, it will help the forum help you if you can use the xl2bb addin (see below) to post a mini sample of your worksheet(s)

But, to answer your question if your calculations involve a range of address like A1:C25, if you insert new data inside that range it will be included in calculations. But, this is why using Excel Tables instead of ranges is generally more effective. You just past the new data on the very next row below the table and any formulas that use the table naming system will be updated.

 

[vladi305]

Ok so you're saying the data source shouldbe in a table?

 

[awoohaw]

no, it should be.
You add new data to the table at the first line under the table.

 

[awoohaw]

Mr Excel 3.xlsm
	A	B	C	D	E	F	G	H	I
1
2	Col A	Col B	Col C
3	12	12	13
4	25	11	15
5	15	13	19
6	20	21	24
7	10	17	16
8				<<<<<	<<<<<	<<<<	4	1	8
9				Drag to cell A8 and the it will add			6	4	4
10				to the table			8	10	3
11							2	8	7
12							5	7	10
13
Sheet2

 

[awoohaw]

if you copy and paste that, you may need to define the table.

 

[vladi305]

Mr Excel 3.xlsm
	A	B	C	D	E	F	G	H	I
1
2	Col A	Col B	Col C
3	12	12	13
4	25	11	15
5	15	13	19
6	20	21	24
7	10	17	16
8				<<<<<	<<<<<	<<<<	4	1	8
9				Drag to cell A8 and the it will add			6	4	4
10				to the table			8	10	3
11							2	8	7
12							5	7	10
13
Sheet2

I appreciate your time into this but this isn't going to help. I thought it was something simple to just keep updating the data
I want the data for the latest date which is the first row to 366 row which is a year

I want to just add a new row from only one worksheet and not having to insert new rows on every worksheet. It's time consuming

 

[awoohaw]

Then maybe you need to use power query. You set the query up once, and when you need to add new data you just need to be sure you put it in the correct location.

Anyway, best wishes.

 

[vladi305]

Then maybe you need to use power query. You set the query up once, and when you need to add new data you just need to be sure you put it in the correct location.

Anyway, best wishes.

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

 

[Fluff]

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