Formula Help appears #ref

[Legacy 330376]

Hi All,

I am trying to avoid #ref appearing when I delete a row from the source spreadsheet. Does anyone have a solution. I want to avoid using Indirect function as the source spreadsheet needs to be open, for it to work.

Any ideas, would be great!!

The formulas are below, that I am trying

=INDEX([MS.xlsm]C!A3,ROWS(A3:A3)):INDEX([MS.xlsm]C!A3,COUNTA([MS.xlsm]C!A3))

=OFFSET(MS.xlsm]C!A3,0,0)

 

[Legacy 330376]

Hi Jason,

Yes- I looked into the morefunc add-in function, however it will be risky to use in my project as multiple users will be using different excel instances.

Good news is your index formula worked. I tested it by deleting a row from the source spreadsheet and it shifts the cell up from the destination spreadsheet.
Fantastic!

=INDEX([MS.xlsm]C!$A:$X,ROW(A1),Column(A1))

 

[GlennUK]

Hi Glenn- Unfortunately Jason is right. The named ranges is causing #ref.

Hi Jason- Thank you for your reply aswell. With the indirect function, I have tried this previously and when the source worksheet is closed it causes #ref to appear on the destination workbook. Have you come across this issue, or know a way to get round it.

I will try the alternative index formula you have provided. Will get back to you.

No, named ranges do not cause problems if you follow my suggestion of naming entire columns.

 

[Legacy 330376]

Hi Glenn-

I tried doing it, will have a look again later as an alternative option.

Thanks for your help.