Error on formula after deleting a line on related sheet

Dear Friends,

I have a problem that I can't fix.

I'm using two sheets, on sheet2 I have formulas related with sheet1.
When I delete a row on sheet1, I loose the formula of the cooresponding row on sheet2...

Here you have an example of the formula of first content row of sheet2.

{=IF(ISERR(INDEX(sheet1!A$2:O$1008,SMALL(IF($E$1=sheet1!A$2:A$1008,ROW(sheet1!A$2:A$1008)-MIN(ROW(sheet1!A$2:A$1008))+1,""),ROW(sheet1!E1)),COLUMN(sheet1!E1))),"",IF(INDEX(sheet1!A$2:O$1008,SMALL(IF($E$1=sheet1!A$2:A$1008,ROW(sheet1!A$2:A$1008)-MIN(ROW(sheet1!A$2:A$1008))+1,""),ROW(sheet1!E1)),COLUMN(sheet1!E1))="","",INDEX(sheet1!A$2:O$1008,SMALL(IF($E$1=sheet1!A$2:A$1008,ROW(sheet1!A$2:A$1008)-MIN(ROW(sheet1!A$2:A$1008))+1,""),ROW(sheet1!E1)),COLUMN(sheet1!E1))))}

If I delete first content row on sheet1, I get the following error formula on second content row of sheet2

{=IF(ISERR(INDEX(sheet1!A$2:O$1006,SMALL(IF($E$1=sheet1!A$2:A$1006,ROW(sheet1!A$2:A$1006)-MIN(ROW(sheet1!A$2:A$1006))+1,""),ROW(sheet1!#REF!)),COLUMN(sheet1!#REF!))),"",IF(INDEX(sheet1!A$2:O$1006,SMALL(IF($E$1=sheet1!A$2:A$1006,ROW(sheet1!A$2:A$1006)-MIN(ROW(sheet1!A$2:A$1006))+1,""),ROW(sheet1!#REF!)),COLUMN(sheet1!#REF!))="","",INDEX(sheet1!A$2:O$1006,SMALL(IF($E$1=sheet1!A$2:A$1006,ROW(sheet1!A$2:A$1006)-MIN(ROW(sheet1!A$2:A$1006))+1,""),ROW(sheet1!#REF!)),COLUMN(sheet1!#REF!))))}

Instead of #REF! I should have E2...

Can someone help me?

Thanks in advance.

Files attached.

Regards,
D. Duarte

Hi there,

Thanks for helping.

With your suggestion I get the results from column A, not column G.

What need to be changed?

Thanks,
D. Duarte

Ok, I got it...

I'm using:

ROWS(Sintra!G$1:G1) COLUMNS(Sintra!A$1:G1)

ROWS(Sintra!G$1:G2) COLUMNS(Sintra!A$1:G2)

ROWS(Sintra!G$1:G3) COLUMNS(Sintra!A$1:G3)

Now if I delete the first content row of sheet1, on the second content row of sheet2 I get the same content of first content row of sheet2...

Maybe I'm still doing something wrong with your suggestion...

Can you check the attached files and modify them with your suggestion?

Sorry

Hi,

Thanks again.

Your last suggestion returns an empty result, even before deleting any row on sheet1 (Sintra).

Regards,
D. Duarte

Here you have.

I noticed that after ENTER, I get the following:

ROWS(#ref!#REF!$1:1));COLUMNS($A:G)

Hello XOR LX,

A very busy week...

Well that's what happens after modifying the formula with ROWS($1:1) and COLUMNS($A:G) and hit the enter key...

Thanks,
D. Duarte

Hello there,

Sorry for the long delay, I've been extremely busy

I tested your sheet, it seems to be better than mine, but something is not working ok.

On Cartão Sintra sheet if you change E1 from LS-02 to LS-04, you will get B4 empty, why is that?

Sorry to disturb you with this...

D. Duarte

Maybe that's cause I'm using open office...

Your second option works ok
I will have to test it with diferent scenarios, but I guess it's the right option.

Thanks a lot, I will confirm if it works as I need.

D. Duarte