Keep Referenced Cell in a Different Workbook, Even If The Data In The Cell Changes

[AcornNut]

Hello,
I'm stumped over something I'm sure is easy. I have 2 separate workbooks, and cell A1 in workbook A currently references the value in A5 in workbook B. Sometimes rows in workbook B get added or deleted, which changes the data. For example, if "apples" are in workbook B in A5, and a row gets added above it, then "apples" will move to A6. What happens then is the formula in A1 in workbook A automatically changes to A6 in workbook B. I need it to continue to reference A5 in workbook B and not "follow the apples".

The formula I've used in A1 in workbook A is: ='[Workbook_B_FileName.xlsx]Tab_Name'!$A$5"
I've tried it absolute, relative, and mixed references and get the same result each time. When adding or removing rows, A5 changes to A6 or A4 (following the apple). I need it to always refer to A5 regardless of what happens to the referenced cell data.

Again, I'm sure it's super easy/basic, but I'm drawing a blank. Any help would be greatly appreciated!!

 

[Jeffrey Mahoney]

You could try this:
=OFFSET([Book4]Sheet1!$A$1,4,0)

The problem still remains if you insert a row above Row 1 in WB B then it will reference 4 rows below Cell A2

 

[Jeffrey Mahoney]

This would be your better choice
=INDEX([Book4]Sheet1!$A:$A,5,1)

 

[dreid1011]

You can use INDIRECT to get around the cell changing.

=INDIRECT("'"&"[Workbook_B_FileName.xlsx]Tab_Name"&"'!"&"A5")

Only while workbook B is open though.

 

[AcornNut]

Thanks to both of you for your help. Dreid1011's reply worked the best in my circumstance! Much appreciated!!!

 

[dreid1011]

You're welcome and happy to help.