Question about Absolute reference formula

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

My first question is really a "why" question. It's not a problem, I
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 and
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 absolute
reference? And what would happen if I changed it to a relative
reference so I can copy the formula to other cells in Workbook 2?

Thanks for your help.


---
Message posted from http://www.ExcelForum.com/

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)


"Maria " wrote in message
...
Hi everyone. This is my first post here. I just found this place and
it looks like a treasure-trove of information. I'm sure I'll be
visiting this site quite often.

My first question is really a "why" question. It's not a problem, I
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 and
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 absolute
reference? And what would happen if I changed it to a relative
reference so I can copy the formula to other cells in Workbook 2?

Thanks for your help.


---
Message posted from http://www.ExcelForum.com/

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 that
exact address: A3"
If you changed it to relative and pasted it somewhere else, cell A3
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.


---
Message posted from http://www.ExcelForum.com/

Pika and Peo – thank you for your prompt reply. I guess my question
stems from the fact that when I put an equal sign and reference a cell
within the same workbook, Excel does not assume I want that cell to be
absolute. It’s only when I reference a cell in a different workbook
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 I
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.

Maria


---
Message posted from http://www.ExcelForum.com/