Inserting a new row changes formula references

[Fabfabian]

Hi everyone,

So I have a spreadsheet with two pages. One with data and another replica of the data page, but with formula in the cells. The formula is a simple IF statement relating to whether or not there is an 'X' in the cell in the data page or not. The formula for cell E6 in the formula page named 'Maxtrix' is:

=IF('Data'!E6="X",Maxtrix!$L$4,0)

Where Maxtrix!$L$4 refers to the value to be given if there is an X in the cell.

All cells in the 'Maxtrix' page refer to the same cell in the 'Data' page such that E6 in the 'Maxtrix' page refers to E6 in the 'Data' page.

The problem I am having is that if I insert a new row above row 6, such that the original row 6 --> row 7 and a new row 6 is inserted, any formula referencing the original row 6 and below (7,8,9,10 etc) now reference the original row 6 that is now row 7, instead of the same row they were referencing before. This means that in the 'Maxtrix' page, the formula in say column E will reference, E1,E2,E3,E4,E5,E7,E8, skipping out the newly made row 6. I have tried adding $ to anchor the numbers in the formula but they still change upon creation of a new row to the 'Data' page.

I know I may have explained this badly and I will attach a sample spreadsheet when I find out how.

Any help in this matter would be greatly appreciated.

[AliGW]

Welcome to the forum!

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Fabfabian]

Thanks AligGW, I have now attached the spreadsheet for use

[Fabfabian]

Any help at all would be really appreciated everyone