Pasting Cross-workbook 3-D reference formula problems

  • Thread starter Thread starter Aaron
  • Start date Start date
A

Aaron

I am trying to populate a workbook from several other
workbooks.

I am able to link an individual cell from one
worksheet/workbook combo to the other (something like cell
A1 "= [Book3.xls]Sheet1'!$A$1"

But when I try to paste that formula into additional
cells, the formula is copied verbatem from the initial
cell (again, "= [Book3.xls]Sheet1'!$A$1").

This is contrary to the way Excel handles 3-D references
from within the same workbook. In that situation, the
result of the pasting would be ""= [Book3.xls]Sheet1'!
$A$2".

Is there something I am missing?

In the end, I am looking for the best way for me to
populate a single workbook from several other workbooks?

Any help would be greatly appreciated.
 
I will answer my own question...

The difference is that when creating the 3-D reference
across workbooks, the process of selecting the cell,
typing "=" and then select the cell from the second
workbook results in absolute symbols ("$") around the cell
location.

This answer was found by looking at Clara's seemingly
unrelated question from earlier this morning.
 
But did you work out how to fix it??

Just hit F4 3 times when you click on the cell and create the formula and it will turn it from
absolute to relative. :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



Aaron said:
I will answer my own question...

The difference is that when creating the 3-D reference
across workbooks, the process of selecting the cell,
typing "=" and then select the cell from the second
workbook results in absolute symbols ("$") around the cell
location.

This answer was found by looking at Clara's seemingly
unrelated question from earlier this morning.
-----Original Message-----
I am trying to populate a workbook from several other
workbooks.

I am able to link an individual cell from one
worksheet/workbook combo to the other (something like cell
A1 "= [Book3.xls]Sheet1'!$A$1"

But when I try to paste that formula into additional
cells, the formula is copied verbatem from the initial
cell (again, "= [Book3.xls]Sheet1'!$A$1").

This is contrary to the way Excel handles 3-D references
from within the same workbook. In that situation, the
result of the pasting would be ""= [Book3.xls]Sheet1'!
$A$2".

Is there something I am missing?

In the end, I am looking for the best way for me to
populate a single workbook from several other workbooks?

Any help would be greatly appreciated.
.
Click to expand...
Click to expand...
 
Back
Top