Dear Mr.Excel,
I am struggling with OFFSET function when source row is deleted.
Here below is basic example:
1) Sheet2.Range("A2:R50") is my table with data
2) Sheet1.Range("A5:R20") is my table with offset data + scrollbar.
If I remove any row in table 1), I automatically get #REF in Offset formula.
I've heard it could be possible to use Offset + Indirect in combination to avoid that problem so Offset can be referenced on cell address instead of data in cell.
Right now I use formula:
But I would like to switch it to OFFSET + Indirect (or Index(Match*)
Could you help me make such a formula?
I am struggling with OFFSET function when source row is deleted.
Here below is basic example:
1) Sheet2.Range("A2:R50") is my table with data
2) Sheet1.Range("A5:R20") is my table with offset data + scrollbar.
If I remove any row in table 1), I automatically get #REF in Offset formula.
I've heard it could be possible to use Offset + Indirect in combination to avoid that problem so Offset can be referenced on cell address instead of data in cell.
Right now I use formula:
Code:
=IF(OFFSET(Database_Copy!A1,$A$6,0)="","",OFFSET(Database_Copy!A1,$A$6,0))
But I would like to switch it to OFFSET + Indirect (or Index(Match*)
Could you help me make such a formula?