Cell reference when inserting rows?

[ljubo_gr]

Hi,
Greetings for all!

I have a formula at Sheet2 as ...something(from this beautiful forum)..Sheet1!$A$2...

When I insert 10 rows in Sheet1, formula in Sheet2 changes into ....Sheet1!$A$12..

How to prevent/fix formula to $A$2, how to fixate it to A2

Thank You in advance!!

New Micro Excel Work31-8-24.xlsx

 

[ljubo_gr]

What's wrong now!!???

=SUMPRODUCT((INDEX(Table1[Datum];2))*(ISNUMBER(MATCH(Table1[REZERVOAR];{"S-5";"S-6";"S-7";"S-8";"S-9";"S-12";"S-13";"S-14"};0)))*(Table1[Razlika litara:]))-SUMPRODUCT((Table1[Datum]=$Q$12)*(INDEX(Table1[Datum];ROW(Table1[Datum]))>=12)*(ISNUMBER(MATCH(Table1[REZERVOAR];{"S-5";"S-6";"S-7";"S-8";"S-9";"S-12";"S-13";"S-14"};0)))*(Table1[Razlika litara:]))

"Date and time who are...... " ################ in cell result

It was INDEX solo.
Here is the formula in Sheet1, Table1 is in Sheet2.

=SUMPRODUCT((ROW(Table1[Razlika litara:])>1)*(ROW(Table1[Razlika litara:])<12)*NOT(ISNUMBER(SEARCH(Table1[REZERVOAR];"S-10;S-11")))*Table1[Razlika litara:])-SUMPRODUCT((Table1[Datum]=Sheet1!Q12)*(ROW(Table1[Datum])>=12)*NOT(ISNUMBER(SEARCH(Table1[REZERVOAR];"S-10;S-11")))*Table1[Razlika litara:])

Every workday a new 10 rows are inserted and everything below row 3511 is deleted. So far no problems, formula is working as it should, fraction of a second slow but, hey...

 

[ljubo_gr]

just one small change, instead of "SEARCH" it is better to put "MATCH", slightly quicker

 

[FormR]

just one small change, instead of "SEARCH" it is better to put "MATCH", slightly quicker

Hi, I don't speak for everyone, but personally, I've got no idea what you're trying to do. If you have a new question it's probably best to start a new thread, include a written description of what you are trying to do, include some sample data and what the expected results are for that sample.