Excel copy-paste - DDE prob

Question

Consider two excel work books,say book1 & book2, locn - "C:...\des..\book1" & book2 etc.

case I

I put formumla in cell A1 "=B1", it takes values of B1.

When I copy cell A1 & paste downward through A2 to A7 (eg.), it takes values of cells B2 to B7.

This works as it should.

when using B1 from same sheet (Sheet1, "=B2") or  B1 from sheet (Sheet2, "Sheet2!B1) in the same work book.

But,

Case II

Now B1 is from any sheet of other workbook say book2 as above. (C:.....\book2 ....!B1.

Here when I copy paste A1 cell down through A2 …A7 , it takes value of B1 only where as it shoud be as per case I.

However, if the B1 value is changed all the values of A1...A7 are changing.

I believe that the problem starte after I used "Transpose" option for paste......may be wrong.

Also my charting software is not deteting that, if the DDE server is running? as it used to.

OS: Vista home basic

Answer 1

By default, Excel creates an absolute cell reference when you link to a cell in another workbook:

=[OtherWorkbook.xlsx]Sheet1!$B$1

The $ characters make the cell reference absolute, i.e. the row and column will not change if you copy othe formula or fill it down or to the right. To make the cell reference relative, remove the $ characters:

=[OtherWorkbook.xlsx]Sheet1!B1

Answer 2

I don't know of a way to make Excel create relative cell references by default in links to other workbooks, sorry.

Answer 3

Thanks, it works.

But,

How to make relative reference than absolute forever.?