Copy Cell Referecnces

M

Martin Wheeler

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
 
D

David McRitchie

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.
 
M

Martin Wheeler

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


David McRitchie said:
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 said:
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
Click to expand...
Click to expand...
 
D

David McRitchie

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 said:
Hi David
Thanks for that. It looks like I am going to have to go to VBA.
Ta,
Martin


David McRitchie said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
M

Martin Wheeler

Hi David,
Thanks for that.
Ta,
Martin

David McRitchie said:
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 said:
Hi David
Thanks for that. It looks like I am going to have to go to VBA.
Ta,
Martin


David McRitchie said:
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

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
Click to expand...
Click to expand...
Click to expand...
 
Top