Check if all formula's in a column are linked to the same file/folder

[mohavv]

Hi,

I have a large workbook with a lot of links to different files (with
same layout).

I am looking for a way to check if the formula's in a column are all
linked to the same file or folder.
Folder would actually suit better because the filename stays the same.

Example:

C:\forecast2008\details.xls
C:\forecast2009\details.xls

Column C in workbook "summary.xls" sheet1 links to ....
2008\details.xls
Colums D links to ....2009\details

Can't find a way to use the text of the formula for comparisons.

Cheers,

Harold

 

[jlclyde]

Hi,

I have a large workbook with a lot of links to different files (with
same layout).

I am looking for a way to check if the formula's in a column are all
linked to the same file or folder.
Folder would actually suit better because the filename stays the same.

Example:

C:\forecast2008\details.xls
C:\forecast2009\details.xls

Column C in workbook "summary.xls" sheet1 links to ....
2008\details.xls
Colums D links to ....2009\details

Can't find a way to use the text of the formula for comparisons.

Cheers,

Harold

Harold,
How is Maud? I am sorry, I just could not resist.
Here is a soloution to your quandry.
I a column that is free you are going to write this. This is assuming
that the column you are interested in is A.
=IF(ISERROR(Search("details.xls",A1,1))=True,0,1) Here is how this
reads. If a1 is serached and it can not find details.xls in the cell
it will return a 0, if it can then it will return a 1. then all you
have to do is find the zeros.
Hope this helps,
Jay

 

[Dave Peterson]

There's nothing built into excel that allows you to look at the formula.

You could use a user defined function, though:

Function GetFormula(Rng As Range)
Dim myFormula As String
GetFormula = ""
With Rng.Cells(1)
If .HasFormula Then
If Application.ReferenceStyle = xlA1 Then
myFormula = .Formula
Else
myFormula = .FormulaR1C1
End If
If .HasArray Then
GetFormula = "{=" & Mid(myFormula, 2, Len(myFormula)) & "}"
Else
GetFormula = myFormula
End If
End If
End With
End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=getformula(a1)

After you have it working, you can insert a new column and return the formula
from each of the adjacent cells.