If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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/ |
#2
|
|||
|
|||
Question about Absolute reference formula
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/ |
#3
|
|||
|
|||
Question about Absolute reference formula
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/ |
#4
|
|||
|
|||
Question about Absolute reference formula
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/ |
Thread Tools | |
Display Modes | |
|
|