Question about Absolute reference formula

[Maria]

Hi everyone. This is my first post here. I just found this place an
it looks like a treasure-trove of information. I'm sure I'll b
visiting this site quite often.

My first question is really a "why" question. It's not a problem,
just like to know why this works the way it does.

Here's the situation:
I have two workbooks open.
Workbook 1 (called Test) has this formula in cell A3: =SUM(A1:A2)
I want the results of the formula in Workbook 1 pasted in Workbook 2.
So I switch to Workbook 2 and click on a cell, type the equal sign an
then switch back to Workbook 1 and select cell A3 and press ENTER.
This formula is pasted in the cell and the results are displayed:
=[test.xls]Sheet1!$A$3

My question is this: Why is cell A3 in the formula an absolut
reference? And what would happen if I changed it to a relativ
reference so I can copy the formula to other cells in Workbook 2?

Thanks for your help

 

[Peo Sjoblom]

1. It's because MS assumes that if you link to another waorkbook you want
absolute reference

2. No problems. select the cell ref in the formula bar and take the $ off or
just press F4 3 times,
now you can copy and it will change depending on where you put it

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[pikapika13]

I don't know if this will answer your question...but I will try:
It is absolute bc you told Excel in Workbook2 (when you put in the
sign): "hey...this cell is equal to the EXACT address in workbook1.
And if copy it anywhere else in workbook2...it's going to refer to tha
exact address: A3"
If you changed it to relative and pasted it somewhere else, cell A
will not be referenced anymore.
Depends on what you need: if you need cell $A$3 (the sum of your data
in other locations of your workbook...don't make it relative.
I apologize in advance if I misunderstood your question.
Regards, pika

 

[Maria]

Pika and Peo – thank you for your prompt reply. I guess my questio
stems from the fact that when I put an equal sign and reference a cel
within the same workbook, Excel does not assume I want that cell to b
absolute. It’s only when I reference a cell in a different workboo
that it does. I just didn’t understand why it makes that assumption.
I thought maybe it HAD to be absolute, and if I changed it to relative
it might give me problems later on down the road. It’s good to know
can change it to relative if I want to.

Peo, thanks for the tip about pressing F3 three times!

Thank you both again for your help!! I very much appreciate it.

Mari