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)

 

[GlennUK]

How about using a defined name for the range, and referencing that?

 

[Legacy 330376]

Hi Glenn,

Thank you for your kind e-mail.

Like this? Defined as risk.
Will wait for your confirmation, before testing.

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

 

[GlennUK]

It depends on what you are trying to achieve.

For example, if you want to always point to the 3rd cell in column C, then name column C as Risk, and use

=INDEX([MS.xlsm]C!Risk,3)

 

[Legacy 330376]

Hi Glenn,

The plan is to make one cell work, then autofill it to column X all the way down to row 100.
C is actually the name of the worksheet.

 

[GlennUK]

So, make a named range of entire columns A:X, and then index into that as necessary, maybe.

Are you just trying to retrieve the first 100 rows of data ( cols A thru X ), from the source workbook?

 

[Legacy 330376]

Hi Glenn,
Okay I will try doing that and get back to you.

I think the limit will be 500 rows, no more.

 

[jasonb75]

If the name refers to the same ranges used in the original formula, then it will make no difference, deleting the rows will still cause the #REF error.

With your formula, as soon as you delete row 3, you will get a #REF error, even with a named range.

If you always want to refer to the same range, you either need to use INDIRECT, or a more complex INDEX formula, either in the formula or in a named range.

Looking at your original post, I would guess at one of these.

=INDIRECT("[MS.xlsm]C!A3")

Or

=INDIRECT("[MS.xlsm]C!RC",0)

Or

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

 

[Legacy 330376]

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.

 

[jasonb75]

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 forgot about that problem, you could look at downloading the MOREFUNC add-in which contains an additional function called INDIRECT.EXT which overcomes the issue, although I have heard that it doesn't work correctly with newer versions of excel.