is there a way to search for a specific value in a formula?
 

Hi

I got a lot of named ranges which refer to a specific workbook however
the values.

for example i have a names range with the following value
='[test.xls]Information'!$C$123"

is there a way to go through all the ranges and delete only the
reference to the test workbook?


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=555964

is there a way to search for a specific value in a formula?
 

Sub tester()
Dim wb As Workbook
Dim nName As Name

Set wb = Workbooks("test.xls")

For Each nName In Names
If nName.RefersToRange.Parent.Parent Is wb Then
nName.Delete
End If
Next nName

End Sub

HTH
--
AP

"funkymonkUK" a
écrit dans le message de news:
...

Hi

I got a lot of named ranges which refer to a specific workbook however
the values.

for example i have a names range with the following value
='[test.xls]Information'!$C$123"

is there a way to go through all the ranges and delete only the
reference to the test workbook?


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile:
http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=555964

is there a way to search for a specific value in a formula?
 

if i am correct this will actually delete the named range which is not
exactly what i was looking for as the ranges are used for calculations.


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=555964

is there a way to search for a specific value in a formula?
 

You mean your named range should refer to Information'!$C$123 insetead of
{test.xls]Information!$C$123

Am i right?

Cheers,
--
AP

"funkymonkUK" a
écrit dans le message de news:
...

if i am correct this will actually delete the named range which is not
exactly what i was looking for as the ranges are used for calculations.


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile:
http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=555964

is there a way to search for a specific value in a formula?
 

that is correct what i tried was finding and replacing all "=" with " =
to make excel think I was working with a text and then copy it over t
the other workbook and again finding all " =" with "=" to change i
back into a formula however it still seem to bring some of the link
over

--
funkymonkU
-----------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...fo&userid=1813
View this thread: http://www.excelforum.com/showthread.php?threadid=55596

is there a way to search for a specific value in a formula?
 

Good Morning FunkyMonkUK,

Have you tried doing:

Edit -- Find -- Replace "[test.xls]" with "" -- Replace All ?

Hopefully I haven't missed your point.

Sincerely,


Isaac Maycott

--
I Maycott
-----------------------------------------------------------------------
I Maycotte's Profile: http://www.excelforum.com/member.php...fo&userid=3560
View this thread: http://www.excelforum.com/showthread.php?threadid=55596

is there a way to search for a specific value in a formula?
 

Sub tester()
Dim wb As Workbook
Dim nName As Name

Set wb = Workbooks("test.xls")

For Each nName In Names
If nName.RefersToRange.Parent.Parent Is wb Then
nName.RefersTo = _
"=" & _
ActiveSheet.Name & _
"!" & _
nName.RefersToRange.Address
End If
Next nName

End Sub

Cheers,
--
AP

"funkymonkUK" a
écrit dans le message de news:
...

that is correct what i tried was finding and replacing all "=" with " ="
to make excel think I was working with a text and then copy it over to
the other workbook and again finding all " =" with "=" to change it
back into a formula however it still seem to bring some of the links
over.


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile:
http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=555964

is there a way to search for a specific value in a formula?
 

i tried that with no luck i think you can see where i am coming from

--
funkymonkU
-----------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...fo&userid=1813
View this thread: http://www.excelforum.com/showthread.php?threadid=55596