• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

Sheet# in Refrence changed to #REF

Hi All

I have large Excel sheets which have 1000s refrences to differet cells in one another. We upgraded from Office 2007 to 2010 & now all the sheet#s in celkl refrences are changed to #REF.

e.g: Cell G2 had a reference before ='File Path\[2011-4XX.xlsx]457'!J9. where 2011-4XX.xlsx is workbook & sheet is 457 & Cell is J9. NOW
Cell G2 have this reference ='File Path\[2011-4XX.xlsx]#REF'!J9. where 457 is changed to #REF & due to this all of cells have invalid reference.

I have made column A with all the sheet Nos. Let say G2 was referenced to sheet 457. Now i have A2 with value of 457 (manual input).
what i need a kind of VBA code which picks the value from adjecent cell from column A & change all #REF in formula to that value. So everything comes back to normal.

Hope this explains well.

I appreciate your support
0
shahzamm
Asked:
shahzamm
  • 13
  • 12
1 Solution
 
gowflowCommented:
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
0
 
shahzammAuthor Commented:
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
0
 
gowflowCommented:
I follow you and I dont
if it is all in 1 workbook can you post it ?
gowflow
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
gowflowCommented:
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
0
 
shahzammAuthor Commented:
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
0
 
gowflowCommented:
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
0
 
shahzammAuthor Commented:
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
0
 
gowflowCommented:
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
0
 
gowflowCommented:
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

Open in new window

0
 
shahzammAuthor Commented:
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.
0
 
gowflowCommented:
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
0
 
shahzammAuthor Commented:
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
0
 
gowflowCommented:
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

Open in new window

0
 
shahzammAuthor Commented:
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
0
 
shahzammAuthor Commented:
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
0
 
gowflowCommented:
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
0
 
shahzammAuthor Commented:
please find the attached
2011-4XX.xlsx
2011-MIS---Copy---Copy.xlsm
0
 
gowflowCommented:
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
0
 
shahzammAuthor Commented:
0
 
shahzammAuthor Commented:
check this.

saving with macro causing this problem
2011-MIS---Copy---Copy.xls
0
 
gowflowCommented:
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

Open in new window

0
 
shahzammAuthor Commented:
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..
0
 
gowflowCommented:
Oh I c
This is not how you explained it at first anyway this version will give you the content of Col A in the formula and not the Refrence of the cell ! Pls delete the old FixREF sub and replace it by this one.
Pls let me know.
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", WS.Cells(CelFormula.Row, "A").Value)
            '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

Open in new window

0
 
shahzammAuthor Commented:
excellent Help.
0
 
gowflowCommented:
Am glad we got it right finally.
gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 13
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now