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

 

[Scott R]

you could use OFFSET(Sheet1!A1,1,0) to always refer to Sheet1!A2

 

[FormR]

Hi, you could also try like this ....INDEX(Sheet1!$A:$A,2).... which avoids the volatile OFFSET() function.

 

[ljubo_gr]

you could use OFFSET(Sheet1!A1,1,0) to always refer to Sheet1!A2

Volatile, "Do you want to save changes...."

 

[ljubo_gr]

Hi, you could also try like this ....INDEX(Sheet1!$A:$A,2).... which avoids the volatile OFFSET() function.

INDEX:INDEX volatile in E2013, INDEX((Sheet1!$A:$A,2) is working, any other combination, NOT WORKING eg. INDEX(whole col, COUNTA(whole col))...ROW(whole col)

 

[FormR]

See here

Small excerpt.

A small number of Excel functions are "volatile". Volatile functions trigger recalculation on every worksheet change, so they can have a drastic impact on worksheet performance. In workbooks that contain a small amount of data, the performance impact may not be noticeable. But in workbooks with large data sets and lots of formulas, adding a volatile function can make the worksheet feel sluggish. In extreme cases, this can make a spreadsheet almost unusable.

 

[FormR]

INDEX:INDEX volatile in E2013

Not in the sense that it re-calculates every time anything in the workbook does. So no real downside other than a "do you want to save" message when you haven't changed anything.

 

[ljubo_gr]

I have two solution, non-volatile, Excel 2013, they are: USE TABLE FOR DATA RANGE and VBA CODE TO INSERT FORMULA EVERY TIME CODE IS TRIGGERED. I'll go for TABLE, VBA code is a bit complicated eg. R1C1 yadda yadda. After rows insertion TABLE is the same and no cell reference change.

 

[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

 

[FormR]

What's wrong now!!???

This seems to be a completely different question, but my guess would be that the formula is returning a negative number and you can't format negative numbers as date/time.