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!

 

[Norie]

I suppose you could use INDIRECT.

=INDIRECT(ARK_B()&"$N$15")

 

[Razzy]

I suppose you could use INDIRECT.

=INDIRECT(ARK_B()&"$N$15")

Works m8!

Maybe it would be better to have an UDF like: "ARK_B(N15)"

When I input "N15", I get N15 in ARK_B.

Can you help me with this UDF? I will try meanwhile offcurse.

Edit: Maybe skip INDIRECT function all together!

indirect-excels-most-evil-function/

 

[Razzy]

Function ARK_C(X As Variant) As String
    Application.Volatile
    ARK_C = Worksheets(3).Name & "!" & X
  
End Function

hm...

 

[Norie]

If you really wanted I suppose you could have something like this.

Function ARK_B(strAddress As String, Optional boolRowAbsolute As Boolean = False, _
                                     Optional boolColAbsolute As Boolean = False, _
                                     Optional lngRefStyle As XlReferenceStyle = xlA1) As String
    Application.Volatile
    
    ARK_B = "'" & Worksheets(2).Name & "'!" & Range(strAddress).Address(boolRowAbsolute, boolColAbsolute, lngRefStyle)
    
End Function

P.S. Why do you need this? How is the sheet reference dynamic?

 

[Razzy]

I have a template workbook that with a custom built report.

When I run a VBA-code I get new Sheets in it, and it would be nice that that the Sheet references where dynamic.

I can live with the indirect formula, can we solve it with that?

I am sorry but I did not get your code to work. But thx very much!

Eksempel.xlsm
	D	E	F
8	699	699	699
9	33	33	33
10	592	592	592
11	289	289	289
12	43	43	43
13	141	141	141
14	218	218	218
15	844	844	844
16	279	279	279
17	369	369	369
Oversikt

Cell Formulas
Range	Formula
D8,F8	D8	=(kanin_sammenstilling!$J$11+kanin_sammenstilling!$J$25)/kanin_sammenstilling!$G$27
E8	E8	=(hest_sammenstilling!$J$11+hest_sammenstilling!$J$25)/hest_sammenstilling!$G$27
D9,F9	D9	=(kanin_sammenstilling!$J$12+kanin_sammenstilling!$J$26)/kanin_sammenstilling!$G$27
E9	E9	=(hest_sammenstilling!$J$12+hest_sammenstilling!$J$26)/hest_sammenstilling!$G$27
D10,F10	D10	=(kanin_sammenstilling!$J$13+kanin_sammenstilling!$J$27)/kanin_sammenstilling!$G$27
E10	E10	=(hest_sammenstilling!$J$13+hest_sammenstilling!$J$27)/hest_sammenstilling!$G$27
D11,F11	D11	=(kanin_sammenstilling!$J$14+kanin_sammenstilling!$J$28+kanin_sammenstilling!$J$29)/kanin_sammenstilling!$G$27
E11	E11	=(hest_sammenstilling!$J$14+hest_sammenstilling!$J$28+hest_sammenstilling!$J$29)/hest_sammenstilling!$G$27
D12,F12	D12	=(kanin_sammenstilling!$J$15+kanin_sammenstilling!$J$31)/kanin_sammenstilling!$G$27
E12	E12	=(hest_sammenstilling!$J$15+hest_sammenstilling!$J$31)/hest_sammenstilling!$G$27
D13,F13	D13	=(kanin_sammenstilling!$J$16+kanin_sammenstilling!$J$30)/kanin_sammenstilling!$G$27
E13	E13	=(hest_sammenstilling!$J$16+hest_sammenstilling!$J$30)/hest_sammenstilling!$G$27
D14,F14	D14	=(kanin_sammenstilling!$J$21)/kanin_sammenstilling!$G$27
E14	E14	=(hest_sammenstilling!$J$21)/hest_sammenstilling!$G$27
D15,F15	D15	=(kanin_sammenstilling!$J$33)/kanin_sammenstilling!$G$27
E15	E15	=(hest_sammenstilling!$J$33)/hest_sammenstilling!$G$27
D16,F16	D16	=(kanin_sammenstilling!$J$34)/kanin_sammenstilling!$G$27
E16	E16	=(hest_sammenstilling!$J$34)/hest_sammenstilling!$G$27
D17,F17	D17	=(kanin_sammenstilling!$J$32)/kanin_sammenstilling!$G$27
E17	E17	=(hest_sammenstilling!$J$32)/hest_sammenstilling!$G$27

 

[Norie]

How did you try the code I posted and how did it not work?

How are you populating the formulas in the sheets you are creating?

 

[Razzy]

Function ARK_C(X As String) As String
    Application.Volatile
    ARK_C = "=" & Worksheets(3).Name & "!" & X
End Function

If I input "N15" I get the =kanin_sammenstilling!N15, but I need to recalculate the cell to get it to work.

I will try your code again now.

The formulas are preset in the template

Edit:

Maybe I need to add an "run" something:

Public Function MyUDF(Value As Variant) As Variant
    MyUDF = Application.Run("ABC", Value, "Value_Name")
End Function

 

[Razzy]

Function Norie(strAddress As String, Optional boolRowAbsolute As Boolean = False, _
                                     Optional boolColAbsolute As Boolean = False, _
                                     Optional lngRefStyle As XlReferenceStyle = xlA1) As String
    Application.Volatile
   
    Norie = "'" & Worksheets(3).Name & "'!" & Range(strAddress).Address(boolRowAbsolute, boolColAbsolute, lngRefStyle)
   
End Function

Eksempel.xlsm
	J
28	#VERDI!
29	#VERDI!
Oversikt

Cell Formulas
Range	Formula
J28	J28	=Norie()
J29	J29	=Norie(N15)

 

[Razzy]

Eksempel.xlsm
	J
29	'kanin_sammenstilling'!N15
Oversikt

Cell Formulas
Range	Formula
J29	J29	=Norie("N15")