Unable to create relative reference to another workbook

[DGT32]

I am trying to create a simple relative cell reference to a cell in another workbook which copies the content from that cell to my current worksheet. When I do, it creates an absolute cell reference, which works for that particular cell. But if I copy the formula down to the next cell, it is still pointing absolutely to the original cell in the other worksheet. If I try to remove the $ in the formula to make it a relative cell reference, Excel (2013) simply leaves the formula in the cell without calculating it. What am I doing wrong? I looked in both this forum and online in general and could not find this particular issue. Thank you.

 

[JLGWhiz]

=[job1.xlsx]Sheet1!A2

If you use these conventions to construct your formula, it should work.

 

[DGT32]

Thanks. I do have my formula constructed that way. =[Registration download test.xlsx]owssvr!E2 But when I hit enter, the formula itself remains visible in the cell, and not the result of the formula. (almost as if the cell were preceded by an apostrophe to make it a comment.)

 

[DGT32]

This behavior started on other cells that were not using references - I think it was an Excel issue - fixed by restarting.

 

[TimNevins]

Format the cell(s) as "General", most importantly not "Text". Cells formatted as "Text" will not evaluate as formulas.