excel data retrieval help

vr_scar 

To extract specific data from worksheets that begin with a specific name pattern and meet certain conditions, you can use VBA (Visual Basic for Applications) in Excel.

Here is an example code that demonstrates how you can achieve this:

Vba code:

Sub ExtractDataFromWorksheets()
    Dim ws As Worksheet
    Dim totalsWorksheet As Worksheet
    Dim i As Long
    Dim rowCounter As Long
    Dim dataRange As Range
    
    ' Set the totals worksheet where the extracted data will be placed
    Set totalsWorksheet = ThisWorkbook.Worksheets("Totals")
    
    ' Initialize the row counter for placing the extracted data
    rowCounter = 1
    
    ' Loop through all worksheets
    For Each ws In ThisWorkbook.Worksheets
        ' Check if the worksheet name begins with "Week Start"
        If Left(ws.Name, 10) = "Week Start" Then
            ' Define the data range to extract from each worksheet
            Set dataRange = ws.Range("A3:B7")
            
            ' Loop through each row in the data range
            For i = 1 To dataRange.Rows.Count
                ' Check the conditions in cells A3-A7 and B3-B7
                If dataRange.Cells(i, 1).Value Like "*A" Then
                    ' Place the data in "Totals!" cell 1
                    totalsWorksheet.Cells(rowCounter, 1).Value = dataRange.Cells(i, 2).Value
                ElseIf dataRange.Cells(i, 1).Value Like "*B" Then
                    ' Place the data in "Totals!" cell 2
                    totalsWorksheet.Cells(rowCounter, 2).Value = dataRange.Cells(i, 2).Value
                End If
                
                ' Increment the row counter
                rowCounter = rowCounter + 1
            Next i
        End If
    Next ws
End Sub

To use this code:

1. Press ALT+F11 to open the VBA editor in Excel.
2. Insert a new module by going to "Insert" > "Module".
3. Copy and paste the above code into the module.
4. Modify the worksheet name "Totals" to match the name of your totals worksheet.
5. Run the code by pressing F5 or by going to "Run" > "Run Sub/UserForm".

The code will loop through all worksheets in the workbook and extract data from worksheets that begin with "Week Start". It will check the conditions in cells A3-A7 and B3-B7, and place the data in the appropriate cells in the "Totals" worksheet.

Make sure to save your workbook as a macro-enabled file format (e.g., .xlsm) to retain the VBA code.

Note: If you have a large number of worksheets or a significant amount of data, the code may take some time to run.

If you prefer to achieve this without using VBA, you can use formulas and functions in Excel.

Here is an approach to extract the data without VBA:

1. In the "Totals" worksheet, enter the following formula in cell 1:

=IFERROR(INDEX('Week Start*'!$B$3:$B$7, SMALL(IF('Week Start*'!$A$3:$A$7="*A", ROW('Week Start*'!$A$3:$A$7)-ROW('Week Start*'!$A$3)+1), ROW(A1))), "")

This formula retrieves the values from cells B3 to B7 on worksheets that begin with "Week Start" and have values in cells A3 to A7 ending with "A".

1. Press Ctrl+Shift+Enter to enter the formula as an array formula. The formula will display the result for the first set of criteria.
2. Copy cell 1 and paste it into cell 2 in the "Totals" worksheet.
3. Modify the formula in cell 2 to the following:

=IFERROR(INDEX('Week Start*'!$B$3:$B$7, SMALL(IF('Week Start*'!$A$3:$A$7="*B", ROW('Week Start*'!$A$3:$A$7)-ROW('Week Start*'!$A$3)+1), ROW(A1))), "")

1. Press Ctrl+Shift+Enter to enter the formula as an array formula. The formula will display the result for the second set of criteria.
2. Copy and paste the formulas in cells 1 and 2 to the desired range in the "Totals" worksheet.

The formulas will dynamically retrieve the values from the specified range on the worksheets that meet the criteria you mentioned. As you copy the formulas to different cells, they will adjust the references accordingly.

Note: Remember to update the ranges ('Week Start*'!$A$3:$A$7 and 'Week Start*'!$B$3:$B$7) to match the actual ranges in your worksheets. Additionally, make sure to include the asterisk (*) after "Week Start" to capture all worksheets with names starting with "Week Start".

Since no one has answered it for at least one day or more, I entered your question in various AI. The text and the steps are the result of various AI's put together.

My answers are voluntary and without guarantee!

Hope this will help you.