Copy Cell Referecnces
 

xl2003

I need to build a summary table which will be full of references such as
=R1!E3.

My problem is that I have about 1200 existing workbooks that I need to paste
the table into.

And when I do it looks like =[12Sept05.xls]R1!E3. Is there any way I can
paste the ref. but without listing the original book.

So I want to copy =[12Sept05.xls]R1!E3 and then paste =R1!E3. The finished
table would have about 50 such refs.

Any help would be greatly appreciated.

Ta,
Martin

Hi Martin,
I would avoid naming sheets with names that look like cell names
whether it works or not. You certainly cannot use defined names
that can't be distinguished from cell addresses.

Copy the formula from the formula bar.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Martin Wheeler" wrote in message ...
xl2003

I need to build a summary table which will be full of references such as
=R1!E3.

My problem is that I have about 1200 existing workbooks that I need to paste
the table into.

And when I do it looks like =[12Sept05.xls]R1!E3. Is there any way I can
paste the ref. but without listing the original book.

So I want to copy =[12Sept05.xls]R1!E3 and then paste =R1!E3. The finished
table would have about 50 such refs.

Any help would be greatly appreciated.

Ta,
Martin

Hi David
Thanks for that. It looks like I am going to have to go to VBA.
Ta,
Martin


"David McRitchie" wrote in message
...
Hi Martin,
I would avoid naming sheets with names that look like cell names
whether it works or not. You certainly cannot use defined names
that can't be distinguished from cell addresses.

Copy the formula from the formula bar.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Martin Wheeler" wrote in message
...
xl2003

I need to build a summary table which will be full of references such as
=R1!E3.

My problem is that I have about 1200 existing workbooks that I need to
paste
the table into.

And when I do it looks like =[12Sept05.xls]R1!E3. Is there any way I can
paste the ref. but without listing the original book.

So I want to copy =[12Sept05.xls]R1!E3 and then paste =R1!E3. The
finished
table would have about 50 such refs.

Any help would be greatly appreciated.

Ta,
Martin

Hi Martin,
If copying from the formula bar won't work for you then
see "copy formula" and "paste formula" macros that
you can set up on your context menu (right click menus).

Right Click Menus (Context Menus) in Excel
http://www.mvps.org/dmcritchie/excel/rightclick.htm

also see http://www.mvps.org/dmcritchie/excel/paste.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Martin Wheeler" wrote in message ...
Hi David
Thanks for that. It looks like I am going to have to go to VBA.
Ta,
Martin


"David McRitchie" wrote in message
...
Hi Martin,
I would avoid naming sheets with names that look like cell names
whether it works or not. You certainly cannot use defined names
that can't be distinguished from cell addresses.

Copy the formula from the formula bar.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Martin Wheeler" wrote in message
...
xl2003

I need to build a summary table which will be full of references such as
=R1!E3.

My problem is that I have about 1200 existing workbooks that I need to
paste
the table into.

And when I do it looks like =[12Sept05.xls]R1!E3. Is there any way I can
paste the ref. but without listing the original book.

So I want to copy =[12Sept05.xls]R1!E3 and then paste =R1!E3. The
finished
table would have about 50 such refs.

Any help would be greatly appreciated.

Ta,
Martin

Hi David,
Thanks for that.
Ta,
Martin

"David McRitchie" wrote in message
.. .
Hi Martin,
If copying from the formula bar won't work for you then
see "copy formula" and "paste formula" macros that
you can set up on your context menu (right click menus).

Right Click Menus (Context Menus) in Excel
http://www.mvps.org/dmcritchie/excel/rightclick.htm

also see http://www.mvps.org/dmcritchie/excel/paste.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Martin Wheeler" wrote in message
...
Hi David
Thanks for that. It looks like I am going to have to go to VBA.
Ta,
Martin


"David McRitchie" wrote in message
...
Hi Martin,
I would avoid naming sheets with names that look like cell names
whether it works or not. You certainly cannot use defined names
that can't be distinguished from cell addresses.

Copy the formula from the formula bar.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Martin Wheeler" wrote in message
...
xl2003

I need to build a summary table which will be full of references such
as
=R1!E3.

My problem is that I have about 1200 existing workbooks that I need to
paste
the table into.

And when I do it looks like =[12Sept05.xls]R1!E3. Is there any way I
can
paste the ref. but without listing the original book.

So I want to copy =[12Sept05.xls]R1!E3 and then paste =R1!E3. The
finished
table would have about 50 such refs.

Any help would be greatly appreciated.

Ta,
Martin

You're welcome, hope it works out for you (copy and paste formulas)

"Martin Wheeler" wrote ...
Hi David,
Thanks for that.