Dynamic sheet references

[Razzy]

Hi!

I got this reference to another sheet:

=kanin_faste!$N$15

I've written an UDF:

Function ARK_B() As String
    Application.Volatile
    ARK_B = Worksheets(2).Name & "!"
End Function

Witchs returns: kanin_faste!

How do I use the UDF inside the formula like:

=ARK_B()$N$15

I saw that ARK_B() returns "kanin_faste!" when I press F9.

hm....


The reason I need this is because I need the sheet references dynamic.

Thank you in advance!

 

[Razzy]

Maybe it is better if I build in a "Search and replace" macro...

 

[Norie]

Have you considered using simple find/replace code to change the sheet names in the template?

That would be straightforward and it would mean you wouldn't need to rely upon INDIRECT.

 

[Razzy]

Have you considered using simple find/replace code to change the sheet names in the template?

That would be straightforward and it would mean you wouldn't need to rely upon INDIRECT.

Thats the key I thing.

Thank you Norie for your help! I can record that macro myself I think!

 

[Razzy]

Sub Makro3()
    Range("D9").Select
    ActiveCell.Replace What:="'2'", Replacement:=Worksheets(4).Name, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Find(What:=Worksheets(4).Name, After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
End Sub

YES! Thanks again Norie

 

[Razzy]

I keep '2' static

 

[Norie]

How many formulas are you dealing with?

 

[Razzy]

How many formulas are you dealing with?

    Cells.Select
    ActiveCell.Replace What:="'1'", Replacement:=Worksheets(2).Name, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Find(What:=Worksheets(2).Name, After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
    ActiveCell.Replace What:="'2'", Replacement:=Worksheets(3).Name, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Find(What:=Worksheets(3).Name, After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
    ActiveCell.Replace What:="'3'", Replacement:=Worksheets(4).Name, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Find(What:=Worksheets(4).Name, After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
    ActiveCell.Replace What:="'4'", Replacement:=Worksheets(5).Name, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Find(What:=Worksheets(5).Name, After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
    ActiveCell.Replace What:="'5'", Replacement:=Worksheets(6).Name, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Find(What:=Worksheets(6).Name, After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Range("A1").Select

About 5

 

[Norie]

How many formulas in total?

 

[Razzy]

How many formulas in total?

The template is not yet finished, is there a limit to how meny "search and replace" Excel can handle? Is that why you ask?

 

[Norie]

The reason I'm asking is because rather than using find/replace across the sheet you could simply loop through all the cells with formulas and do a find/replace at that level.

Obviously how efficient that would be depends on the number of formulas you have.