CODEWITHSUNDEEP
excelexcel-formula
Abx
Abx

Reputation: 1

Excel: Return If result of same Cell in different Sheets

I have different Sheets in the same Workbook with the same pattern and same tables. I need to check for a cell value in these sheets to evaluate for a condition and return its values.

For Example: Cell B2 in Sheet1 through Sheet9 is always a number, in another sheet I need to know which of these numbers are Less than 5. Then return an array with the values.

Upvotes: 0

Views: 56

Answers (1)

atclaus
atclaus

Reputation: 1176

Here is a solution in VBA. Edit the lines that I call out at the top of the macro.

    Option Explicit
    Sub get_from_sheets()
        Dim cell_values(), threshold As Double
        Dim source_cell, output_first_cell, output_sheet As String
        Dim num_sheets, start_sheet, end_sheet, a As Integer
        Dim out_rw, out_cl As Integer

        source_cell = "B2"  'fill in this
        start_sheet = 10    'fill this
        end_sheet = 13      'fill this
        output_sheet = "Sheet14"    'fill this
        output_first_cell = "A1"    'fill this
        threshold = 5       'fill this

        out_rw = Range(output_first_cell).Row
        out_cl = Range(output_first_cell).Column
        ReDim cell_values(end_sheet)

        For a = start_sheet To end_sheet
            cell_values(a) = Sheets("Sheet" & a).Range(source_cell)
        Next a

        For a = start_sheet To end_sheet
            If cell_values(a) < threshold Then
                Sheets(output_sheet).Range(Cells(out_rw, out_cl), Cells(out_rw, out_cl)) = cell_values(a)
                out_rw = out_rw + 1
            End If
        Next a

    End Sub

Upvotes: 1

Related Questions