[RESOLVED] [Excel] Worksheet Variable in Cell Function Formulas?

[funkykizzy]

Hi again!

Is it possible to use a variable worksheet reference in the string of a cell formula? For example, I want to loop through sheets 2-13 (out of 15) and calculate the median for all numerical values in column G and place them in a table. I tried using Function.Median but it doesn't work with my VBA version so I have to use Cell.Value = "cell function" instead. Here is an example of the code below.

Sheets("Table").Cells(i, 3).Value = "=MEDIAN('Analyte1'!G:G)"

Is it possible for me to cycle through the sheets instead of having to type out each worksheet name in the formula? I am working on something like

Dim iSheet As Integer
For iSheet = 2 To 13
        Worksheets(iSheet).Activate
        ' Populate table with stats
        For i = 2 To 13
            Sheets("Table").Cells(i, 3).Value = "=MEDIAN(iSheet!G:G)"
            Sheets("Table").Cells(i, 4).Value = "=MODE(iSheet!G:G)"
            Sheets("Table").Cells(i, 5).Value = "=QUARTILE(iSheet!G:G,1)"
            Sheets("Table").Cells(i, 6).Value = "=QUARTILE(iSheet!G:G,3)"
            Sheets("Table").Cells(i, 7).Value = "=GEOMEAN(iSheet!G:G)"
        Next i
    Next iSheet

but it just looks for the sheet named "iSheet" instead of using 'iSheet' as a variable.

Thanks!!

[funkykizzy]

Thanks everyone! I got a solution:

Sheets("Table").Cells(i, 3).Formula = "=MEDIAN('" & Worksheets(iSheet).Name & "'!G:G)"