Sheet# in Refrence changed to #REF

If I understand your post you have a workbook that contain a formulas that is a refrence to an other workbook that has a sheet called 457 and when you made the upgrade it seems that you changed directory so when you opend the workbook it did not find the other workbook 'File Path\[2011-4XX.xlsx hence it did not find the 457 sheet and changed it to REF.

If my understanding is correct,,
first you need to do is to make sure that
in your file 'File Path\[2011-4XX.xlsx the sheet 457 Exist and is there.

then you select from the menu Edit you choose Replace and put
in Find
#REF
and in replace
457
and you press replace all
Make sure you save the workbook onto a new name in case you mess up something.

Best for you if it is difficult is to post both workbook and I will hv a look at it.
gowflow

Thanks
No its not like that. All the workbooks are in same place as it is.
I need to change #REF in formula. if you link a cell to another cell in another sheet in another workbook you do type in cell like ='File Path\[2011-4XX.xlsx]457'!J9. where [workbook name]sheet#!cell#.

when you click the cell it will show you path of linked cell in formual bar right?

this reference in formula bar is changed to ='File Path\[2011-4XX.xlsx]#REF'!J9. I need to change this #REF. not the #REF in cell

I follow you and I dont
if it is all in 1 workbook can you post it ?
gowflow

What do you mean by
this reference in formula bar is changed to ='File Path\[2011-4XX.xlsx]#REF'!J9. I need to change this #REF. not the #REF in cell
What you see in the formulas bar is the reflection of what is in the cell !!! Have you tried doing a search replace as I proposed ?
gowflow

I can't share the workbook as its confidential. see the screenshot attached. yellow highlighted are cell & its reference in formual bar. Red is the sheet#.
I need to change the #REF shown in formula bar (in red) to sheet# highlighted in Red (e.g: 457)
excel.jpg

you mean in G93 you need the formulas to be
='D:\Newfolder\[2011-4XX.xlsx'A93!J9   ??
Again if it is giving you REF is because it is trying to open the file and it seems it does not find the sheet 457 and it is returning with #REF this is why I asked you you are openeing an other workbook.
Is 'D:\Newfolder\[2011-4XX.xlsx' the name of THISWORKBOOK that we see the screenshot that have this formulas ? if yes then why the whole name at the first place ? just the Sheet!Reference is enough.
gowflow

yes i need formual to be something ='D:\Newfolder\[2011-4XX.xlsx'A93!J9. so the G93 will pick the correct sheet No. This sheet no. is value in A93.
yes [2011-4XX.xlsx] is workbook name & path is D:\Newfolder\

The whole name changes to sheet reference only when you open sheet [2011-4XX.xlsx]

BTW the full path is ='D:\Newfolder\[2011-4XX.xlsx]A93'!J9. during highlighting ] hidden. manully replacing #REF with sheet# (457) reolves the issue, but for 100s of cells & 10s of sheets somehitng should be automated

1) They are all in Col G in this sheet or they are scattered all over the workbook in several sheets ?
2) If they are scattered all over then they are in each sheet in Col G is the formulas and it refers to Col A for the sheet on the same row ??
gowlfow

I don't know if this is what your looking for. Anyway to mak it work pls do the following:

1) Open your workbook that contain the REF in formulas you need to change.
2) Save the workbook onto a new name to make sure we do not mess your original file in case somthing goes wrong.
3) If your using Excel 2003 goto Tools/Macro/Visual Basic Editor
4) on the left pane you will see all your sheets displayed and on top the workbook name. Roght click on the workbook name and choose Insert/Module
5) Module1 will be created doubleclick on it.
6) Press on the button SELECT ALL from the window code here and right click in the code and choose COPY.
7) Paste the code in module1
8) Check on Tools/Macros/Security and make sure Medium is selected and in trusted publisher tab make sure that both addin and trust vba is also ticked. press ok to exit this and SAVE the workbook and close it.
9) Start the workbook and when prompt ENABLE macros.
10) from the tools menu choose Macros and you will see FixREF click on RUN and check the results.

If your using 2007 and need help pls let me know

Sub FixREF()
Dim WS As Worksheet
Dim MaxRow As Long, I As Long, Item As Long

For Each WS In ThisWorkbook.Worksheets
    MaxRow = WS.UsedRange.Rows.Count
    For I = 1 To MaxRow
        If InStr(1, WS.Cells(I, "G").Formula, "#REF") <> 0 Then
            Application.DisplayAlerts = False
            WS.Cells(I, "G").Formula = Replace(WS.Cells(I, "G").Formula, "#REF", "A" & I)
            Application.DisplayAlerts = True
            Item = Item + 1
        End If
    Next I
Next WS

MsgBox ("A total of " & Item & " formula have replaced all '#REF' to their correct values")

End Sub

Select allOpen in new window

thats great help. It is working but manually. It pops up the sheet list window to select the sheet No.
what i need is automated, G93 picks form a93. g100 picks form a100.

can it be that it ask for input of column. REF problem is in 3-4 coulmns in different sheets.

Well I asked you the questio nyou never rpelied so I build it on col G the formulas and col A the alsternative !!!

What are the Col letter when there is a REF ??? I will need to alter the macro and ask you to input a col letter
You need to give me something like this
Formula    Cell Replacement
G                    A
F                    D
etc ... Now the macro works only on G and replace A !
gowflow

the formula cells are G H J K L M N O P Q R S T U.
The replacement coulmn is A.

the formula column could vary but the replacement column will be same.

Please if possible to be automated. No Manual inputs

appriciate your help

ok pls try this version of the macro. Simply delete the old one then copy paste this one in Module1 (to copy it don't forget to click on SELECT ALL in the code window and right click copy and paste in module1) Save the workbook and try again.
pls let me know how it goes.
gowflow

Sub FixREF()
Dim WS As Worksheet
Dim MaxRow As Long, I As Long, Item As Long
Dim CelFormula

For Each WS In ThisWorkbook.Worksheets
    MaxRow = WS.UsedRange.Rows.Count
    For Each CelFormula In WS.UsedRange.SpecialCells(xlCellTypeFormulas)
    'For I = 1 To MaxRow
        If InStr(1, CelFormula.Formula, "#REF") <> 0 Then
        'If InStr(1, WS.Cells(I, "G").Formula, "#REF") <> 0 Then
            Application.DisplayAlerts = False
            CelFormula.Formula = Replace(CelFormula.Formula, "#REF", "A" & CelFormula.Row)
            'WS.Cells(I, "G").Formula = Replace(WS.Cells(I, "G").Formula, "#REF", "A" & I)
            Application.DisplayAlerts = True
            Item = Item + 1
        End If
    'Next I
    Next CelFormula
Next WS

MsgBox ("A total of " & Item & " formula have replaced all '#REF' to their correct values")

End Sub

Select allOpen in new window

This one is not working at all. earlier code was working but manually selcting sheet#.
This one is not working at all. BTW its still manual input.

Please make it automatic

Hi gowflow

ignore my previous reply

the code is working only remaing thing is to automate this process. even in this code it prompts to select sheet # as attached.

final step is to automate this just run Macro & all updated..

most appriciated help
Capture.JPG

ok I am surprised as it works here automatically. Can we do something ?
Pls copy one of your sheets that have the #REF problem in a new workbook (right click on the sheet and choose Move or Copy tick on copy and select Book1 from the upper drop down) save this new workbook and post it here. Also pls post the real book that contain the sheets 410 433 etc ... so I see what is happeneing.
gowflow

please find the attached
2011-4XX.xlsx
2011-MIS---Copy---Copy.xlsm

Well it seems the copy of the sheet changed the formulas as now in 2011-MIS---Copy---Copy.xlsm I have all formulas indicating only =#REF (No file name added to it) I need the file same as it is your end like you post it before with the entire formulas pls chk again if you can save it accordingly.
gowflow

check this
2011-MIS---Copy---Copy.xlsm

check this.

saving with macro causing this problem
2011-MIS---Copy---Copy.xls

Try this version of the macro. I tried it here and still it does not prompt me for anything.
gowflow

Sub FixREF()
Dim WS As Worksheet
Dim MaxRow As Long, I As Long, Item As Long
Dim CelFormula

For Each WS In ThisWorkbook.Worksheets
    MaxRow = WS.UsedRange.Rows.Count
    For Each CelFormula In WS.UsedRange.SpecialCells(xlCellTypeFormulas)
    'For I = 1 To MaxRow
        If InStr(1, CelFormula.Formula, "#REF") <> 0 Then
        'If InStr(1, WS.Cells(I, "G").Formula, "#REF") <> 0 Then
            Application.EnableEvents = False
            Application.DisplayAlerts = False
            
            On Error Resume Next
            CelFormula.Formula = Replace(CelFormula.Formula, "#REF", "A" & CelFormula.Row)
            On Error GoTo 0
            'WS.Cells(I, "G").Formula = Replace(WS.Cells(I, "G").Formula, "#REF", "A" & I)
            Application.DisplayAlerts = True
            Application.EnableEvents = True
            Item = Item + 1
        End If
    'Next I
    Next CelFormula
Next WS

MsgBox ("A total of " & Item & " formula have replaced all '#REF' to their correct values")

End Sub

Select allOpen in new window

still the same. the code si taking the No. of cell not the value of cell.

e.g: the code changes REF to A93 A94 etc..that y its asking for sheet No. it should take the value of A93 & A94 etc..

excellent Help.

Am glad we got it right finally.
gowflow