[Solved] How to get data from different sheets to one sheet

[Koolrk]

Sir, I have a workbook with 5 sheets. Sheet 1 to Sheet 4 we have data from different zones. In Sheet 5 there is a table where I need specific data from Sheet 1 to Sheet4.
I have office 2007 at my workplace and office 365 at my home.
Pls help.

 

[Lutfor Rahman Shimanto]

Sir, I have a workbook with 5 sheets. Sheet 1 to Sheet 4 we have data from different zones. In Sheet 5 there is a table where I need specific data from Sheet 1 to Sheet4.
I have office 2007 at my workplace and office 365 at my home.
Pls help.

Hello Koolrk

Thanks for sharing your problem. In your office, you have Excel 2007. So, you need formulas that are compatible with the Excel 2007 version.

I am delighted to inform you that I have developed some formulas to fulfil your requirements using the IFERROR, AVERAGEIFS, SUMIFS, INDIRECT, CHOOSE, and MATCH functions.

OUTPUT OVERVIEW:

​

Follow these steps:
Step 1: Select cell C5 => insert the given formula.

=IFERROR(
AVERAGEIFS(
INDIRECT("'" & $B$4 & "'!" &
CHOOSE(
MATCH($A$5, {"PARA 1","PARA 2","PARA 3","PARA 4","PARA 5","PARA 6","PARA 7","PARA 8","PARA 9"}, 0),
"B","C","D","E","F","G","H","I","J",
) & "4:" &
CHOOSE(
MATCH($A$5, {"PARA 1","PARA 2","PARA 3","PARA 4","PARA 5","PARA 6","PARA 7","PARA 8","PARA 9"}, 0),
"B","C","D","E","F","G","H","I","J",
) & "34"
),
INDIRECT("'" & $B$4 & "'!A4:A34"),
">=" & $B$2,
INDIRECT("'" & $B$4 & "'!A4:A34"),
"<=" & $B$3
),
"#DIV/0! exists."
)

​

Step 2: Hit Enter to see the result in cell C5.

​

Step 3: Select cell C6 => insert the given formula.

=IFERROR(
SUMIFS(
INDIRECT("'" & $B$4 & "'!" &
CHOOSE(
MATCH($A$6, {"PARA 1","PARA 2","PARA 3","PARA 4","PARA 5","PARA 6","PARA 7","PARA 8","PARA 9"}, 0),
"B","C","D","E","F","G","H","I","J",
) & "4:" &
CHOOSE(
MATCH($A$6, {"PARA 1","PARA 2","PARA 3","PARA 4","PARA 5","PARA 6","PARA 7","PARA 8","PARA 9"}, 0),
"B","C","D","E","F","G","H","I","J",
) & "34"
),
INDIRECT("'" & $B$4 & "'!A4:A34"),
">=" & $B$2,
INDIRECT("'" & $B$4 & "'!A4:A34"),
"<=" & $B$3
),
"#DIV/0! exists."
)

​

Step 4: Hit Enter to see the result in cell C6.

​

Things to Remember: Some of your source data cells contain #DIV/0! You can handle these errors by using the following Excel VBA Sub-procedure.
Steps: Select the intended range => Run the given code.
Excel VBA Code:

Sub ClearErrorsInSelectedRange()

    Dim selectedRange As Range
    Dim cell As Range

    Set selectedRange = Selection

    For Each cell In selectedRange
        If IsError(cell.Value) Then
            cell.Value = 0
        End If
    Next cell
    
End Sub

​

Hopefully, the idea will help you reach your goal. Good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

 

[Koolrk]

Sir, Thank you very much for your precious time and wonderful solution.
Sir, I have one more question. Suppose in the same workbook we have many more "PARAMETERS" i.e PARA 1,PARA 2,........PARA 199,PARA 205 and more to come in future in all the sheets i.e SHEET 1 TO SHEET 6 in the same ROW 2, say A2,B2,C2.............HQ2 etc. Is there a another way that when we enter which parameter (PARA 1,PARA 2 etc) in cell A5 and A6 we can get result based on CELL "B5 and B6" in Cell C5 and C6 of a particular sheet because adding all "PARA 1,PARA 2.......and so on till the end will make the formula very lengthy.
Please help if there is any option.

Regards,
Ramesh