Solved

Sheet# in Refrence changed to #REF

Posted on 2011-09-19
25
376 Views
Last Modified: 2012-05-12
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
Comment
Question by:shahzamm
  • 13
  • 12
25 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 7

Author Comment

by:shahzamm
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I follow you and I dont
if it is all in 1 workbook can you post it ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 7

Author Comment

by:shahzamm
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 7

Author Comment

by:shahzamm
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 7

Author Comment

by:shahzamm
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 7

Author Comment

by:shahzamm
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 7

Author Comment

by:shahzamm
Comment Utility
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
 
LVL 7

Author Comment

by:shahzamm
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 7

Author Comment

by:shahzamm
Comment Utility
please find the attached
2011-4XX.xlsx
2011-MIS---Copy---Copy.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 7

Author Comment

by:shahzamm
Comment Utility
0
 
LVL 7

Author Comment

by:shahzamm
Comment Utility
check this.

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

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 7

Author Comment

by:shahzamm
Comment Utility
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
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
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
 
LVL 7

Author Closing Comment

by:shahzamm
Comment Utility
excellent Help.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Am glad we got it right finally.
gowflow
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now