How to prevent #REF when sheets are deleted and re-added

I have a very large workbook that imports many worksheets from various places.  This is necessary, no linking is possible.  A macro runs, deletes the worksheets from the previous day and copies in the new ones.  I have several hundred cells that are pulling using a complicated formula, and when the sheets are deleted, it causes the formula to get refilled with a #REF reference to the deleted sheet... even though the sheet is replaced with the same name.

The formula is like this:
=INDEX(MySheetThatGetsDeleted!A:P,MATCH(RptDate,MySheetThatGetsDeleted!B:B,0),MATCH("ASA",MySheetThatGetsDeleted!2:2,0))

So I had this idea of creating my own Intersect Function to feed it the sheet name, the RptDate, and the tag to search for, in this case "ASA".  The problem is that I can't use Match in a function.
I had this but it bombs on the Match:
Function ISectPhone(Sheetname, RowTarget, ColTarget) As Variant
Dim sSheetRef As String
Dim sRowRange As String
Dim sColRange As String
sSheetRef = Sheetname & "!A:P"
sRowRange= Sheetname & "!B:B"
sColRange= Sheetname & "!2:2"
ISectPhone = Index(sSheetRef, Match(RowTarget, sRowRange, 0), Match("ASA", sColRange, 0))
End Function


I would call it like this =ISectPhone("MySheetThatGetsDeleted",RptDate,"ASA")

But maybe there is an easier way... let me know any brilliant ideas.  One thing I did try was linking the sheets instead of deleting and re-copying.. problem then is it takes a half an hour for the workbook to open.  Running the macro to delete and recopy takes only a minute.
LVL 1
CoghanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
You can use some functions in VBA, but not all. Prefix them with either Application or WorksheetFunction.

In your case, however, you might be better off using the Evaluate method:
ISectPhone = Application.Evaluate("INDEX(" & sSheetRef & ",MATCH(" & RowTarget & ", " & sRowRange & ", 0), MATCH(""ASA"", " & sColRange & ", 0))")


Brad
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard DanekeTrainerCommented:
Just guessing, but what if you turn off automatic recalculation until you finish copying the worksheets?
0
byundtMechanical EngineerCommented:
You may encounter a problem getting the user-defined function to update when you update the sheets. The standard way is to use Application.Volatile in your UDF. That makes the function recalculate every time anything changes in your workbook. The overhead may be too much (sluggish recalc time), however.

One alternative tactic to consider is to add a parameter to your UDF. This parameter would be used solely to trigger a recalc. In the snippet below, I added the parameter bRecalc. This could be a TRUE/FALSE value in a named range pointing to a cell on a worksheet that won't get deleted. Select that cell, click in the formula bar and hit Enter. All your ISectPhone function calls will now update.

Brad
Function ISectPhone(Sheetname As String, RowTarget As Variant, ColTarget As Variant, bRecalc As Boolean) As Variant
Dim sSheetRef As String
Dim sRowRange As String
Dim sColRange As String
sSheetRef = Sheetname & "!A:P"
sRowRange= Sheetname & "!B:B"
sColRange= Sheetname & "!2:2"
ISectPhone = Application.Evaluate("INDEX(" & sSheetRef & ",MATCH(" & RowTarget & ", " & sRowRange & ", 0), MATCH(""ASA"", " & sColRange & ", 0))")
End Function

Open in new window

0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Richard DanekeTrainerCommented:
Turn off calculation and when finished turn on calculation with a full rebuild.
0
Richard DanekeTrainerCommented:
Sorry, code snipped was dropped.  Here it is and again in Code attached:
Dim oSht as worksheet
Application.Calculation=xlCalculationManual

for each oSht in Worksheets
oSht.enablecalculation=false
        'copy to your sheets in here
osht.enablecalculation=true
next osht

Application.calculatefullrebuild


Dim oSht as worksheet
Application.Calculation=xlCalculationManual

for each oSht in Worksheets
oSht.enablecalculation=false
    'copy to your worksheets in here
osht.enablecalculation=true
next osht

Application.calculatefullrebuild

Open in new window

0
CoghanAuthor Commented:
byundt.. Yes, you are onto something here.  Not quite perfect, partially because I had a typo.  But now I am getting an N/A error which could mean it just isn't matching.  This may be the solution, I'll keep working with it.
0
byundtMechanical EngineerCommented:
I misspoke when suggesting that you didn't need to change the value of the extra parameter bRecalc. You do in fact need to change its value. The UDF steadfastly resists recalculating until you do so.

Brad
0
CoghanAuthor Commented:
This is working.  I changed the function to XPhone but in the end what I had to do is set up intermediate variables along the way and also I have to feed it the named range for the date with quotations.  This now works.

Function XPhone(MySheet, RowTarget, ColTarget) As Variant

Dim sSheetRef As String
Dim sRowRange As String
Dim sColRange As String

sRowRange = MySheet & "!B:B"
sColRange = MySheet & "!2:2"

Dim sMatch1, sMatch2, sIndex

sMatch1 = "Match(" & RowTarget & ", " & sRowRange & ",0)"

sMatch2 = "Match(""" & ColTarget & """, " & sColRange & ",0)"

sMatch2 = Application.Evaluate(sMatch2)

sIndex = "Index(" & MySheet & "!A:P," & sMatch1 & "," & sMatch2 & ")"

XPhone = Application.Evaluate(sIndex)

End Function

Works if I feed it like this: 'note RptDate had to be in quotes.
=XPhone("Total_no_Default","RptDate","ASA")
0
CoghanAuthor Commented:
I had to monkey a little bit with it, but the basic premise was spot on.  Very well done and I can use this same method to simplify more that I do.
0
byundtMechanical EngineerCommented:
Coghan,
Have you tried changing a value on one of the worksheets? You'll find that your function doesn't update. That may or may not be a problem, but I would be remiss if you didn't salute the issue.

The snippet shows a different approach, using Application.Match and Application.Volatile (to force a recalc)

Thanks for the grade and kind words!

Brad
Function XPhone(MySheet, RowTarget, ColTarget) As Variant

Dim sSheetRef As String
Dim RowRange As Range
Dim ColRange As Range
Dim sMatch1, sMatch2, sIndex
Application.Volatile

Set RowRange = Worksheets(MySheet).Range("B:B")
Set ColRange = Worksheets(MySheet).Range("2:2")

sMatch1 = Application.Match(RowTarget, RowRange, 0)
sMatch2 = Application.Match(ColTarget, ColRange, 0)
XPhone = Worksheets(MySheet).Cells(sMatch1, sMatch2)
End Function

Open in new window

0
CoghanAuthor Commented:
Good idea but I have to ctrl alt f9 anyway for other reasons.  Still I'll fool around with it tomorrow and see what happens.  I thought of the Cells thing as well.  Next thing I need is to retrieve the actual address from this.  Next question.. ha ha.
0
byundtMechanical EngineerCommented:
With my last code snippet, changing statement 14 to the following would return the address:
XPhone = Worksheets(MySheet).Cells(sMatch1, sMatch2).Address(External:=True)
0
CoghanAuthor Commented:
I still had to play with a bit, but I did get it working now.  I have another question, but I can post in a new box if more appropriate.   Now that I have the XPhone part working... I need to know how I can return the Worksheets(MySheet).Cells(sMatch1, sMatch2).Address(External:=True)  somehow from the function so that I can hyperlink to that page.  

In other words now I have in the cell this:  =XPhone("Total_ICAN_Sub_no_Default",RptDate,"ASA")
And that returns exactly what I need it to.  And using your suggestion, I was able to get the location of the worksheet so that I can hyperlink to it.  I'd like to be able to create a hyperlink based on the formula result sending the person to the value from Worksheets(MySheet).Cells(sMatch1, sMatch2).Address(External:=True) when they hit a function key.  Want me to post as a new question?
0
byundtMechanical EngineerCommented:
I see that you opened a new question in See http:/Q_26111661.html to cover hyperlinking to the cell being referred to by this cell.

FWIW, a user-defined function being used in a worksheet formula is unable (by Microsoft design) to do anything that changes the user interface. Even if you included code in XPhone that would set a hyperlink, it wouldn't execute.

That said, I came up with a workaround using a Worksheet_BeforeDoubleClick event sub in that other thread.

Brad
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.