OK, looks as though I cannot help on this one.

I seem to be missing something here but I am not sure what.



"gotroots" wrote:


Having the range !$A$1, !$A$2 and !$A$3 included throws a spanner in the
works, I need the sheet not any specfic range within the sheet.



"trip_to_tokyo" wrote:

OK, I think that my example still stands then.

So I had:-

='C:\steve\[Gotroots3.xlsx]Sheet1'!$A$1
='C:\steve\[Gotroots3.xlsx]Sheet1'!$A$2
='C:\steve\[Gotroots3.xlsx]Sheet1'!$A$3

Just substitute Sheet1 above to whatever Sheet it should be referencing
(other than Sheet1).

Does that not give you what you want?

Please hit Yes if my comments have helped.

Thanks.





"Gotroots" wrote:

Hi,

I need the sheet and not the cell range

for example:

='C:\steve\[Gotroots3.xlsx]Sheet1'
='C:\steve\[Gotroots3.xlsx]Sheet2'
='C:\steve\[Gotroots3.xlsx]Sheet3'

originally I would have had in the name range C:\steve\Gotroots3 for the
range A1:A3;

Sheet1
Sheet2
Sheet3

If you get what I mean.



"trip_to_tokyo" wrote:

You will need to quote the full path name in the destination Workbook.

For example:-

1. I have the following Range Name in file called:-

C:\steve\Gotroots3

The Range Name is:-

a1toa3namerange

- found in cells A 1 to A3 of Sheet1.

2. In a file called:-

C:\excel\microsoft\Gotroots2

I have the following in cells A 1 to A 3 of Sheet1:-

='C:\steve\[Gotroots3.xlsx]Sheet1'!$A$1
='C:\steve\[Gotroots3.xlsx]Sheet1'!$A$2
='C:\steve\[Gotroots3.xlsx]Sheet1'!$A$3

Basically you must quote the full path name to access the correct Range Name.

If my comments have helped please hit Yes.

Thanks.



"Gotroots" wrote:

I created a define name range on a sheet of certain worksheets in the
workbook. Many of those sheets have now been moved to other workbooks and as
a result the Name range is no-longer working. What way do I add the external
workbook name and its sheet name.

Thank you for any help.