Copy Cell Referecnces

[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

 

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

 

[Martin Wheeler]

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

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

 

[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

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

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

 

[Martin Wheeler]

Hi David,
Thanks for that.
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

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


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

 

[David McRitchie]

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