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.

Just to add I think I need the complete file name where the sheet is now.

for example:

D:\Excel docs\[TestBook.xlsm]TestSheet

I have tried this and the defined name is not being picked up in any of the
fomulas where it is used.



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

Yes, you need the full path name (see my other posting where I give you a
worked example, which I have tested).

Please hit Yes if my comments have helped.

Thanks.

"Gotroots" wrote:

Just to add I think I need the complete file name where the sheet is now.

for example:

D:\Excel docs\[TestBook.xlsm]TestSheet

I have tried this and the defined name is not being picked up in any of the
fomulas where it is used.



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

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.

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.

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.

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.

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.

Have you tried a sheet querie?

Using menu DataImport External DataNew Database Querie...

In Databases tab select Excel Files*, (make sure wizard check box is ticked
for ease) click OK...
Select the required workbook (browse function is a bit clunky in Excel2003),
click OK...
Select the required sheet and click the button in the middle, click Next 3
times...
Click Finish...
Ensure New Worksheet radio button is selected, Click OK

And you now have a dynamically linked sheet, that will update from the
source file. If you right click anywhere in the data imported and select
Refresh Data you will force the update.

Then set the name range to the data in this range. I would recommend using
the offset function creating a dynamic range.

--
If this is the answer you hoped for please remember to click the yes button
below...

Kind regards

Rik


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

That's another approach worth presuing although I am using Excel2007 so your
notes although very useful seem to be different for the 07version.

Thank you for the advise.

"Rik_UK" wrote:

Have you tried a sheet querie?

Using menu DataImport External DataNew Database Querie...

In Databases tab select Excel Files*, (make sure wizard check box is ticked
for ease) click OK...
Select the required workbook (browse function is a bit clunky in Excel2003),
click OK...
Select the required sheet and click the button in the middle, click Next 3
times...
Click Finish...
Ensure New Worksheet radio button is selected, Click OK

And you now have a dynamically linked sheet, that will update from the
source file. If you right click anywhere in the data imported and select
Refresh Data you will force the update.

Then set the name range to the data in this range. I would recommend using
the offset function creating a dynamic range.

--
If this is the answer you hoped for please remember to click the yes button
below...

Kind regards

Rik


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