Data Filter/Consolidation

[Jedi Master]

I have a master worksheet that looks at 50 identical worksheets like the below:

I want to pull entered info from the 50 identical worksheets to the master and consolidate. For example, Quote Task Worksheet looks like:

and Quote Task Worksheet (2) looks like:

The formula I am looking for would consolidate info entered into all 50 Quote Task Worksheets in the Quote Task Master and look like this:

I am trying to make a version of the following formula work, no luck yet: =IF($M19="","",CHOOSECOLS(FILTER(HSTACK('Quote Task Worksheet '!A50:K73,'Quote Task Worksheet (2)'!A50:K73),'Quote Task Worksheet '!D50:D73>0),1,2,3,4,5,6,7,8,9,10,11))

Not sure how or if I need to filter the multiple sheets, if I am only grabbing entered info, then I dont think I need to filter anything.

 

[mumps]

Are you open to using a macro? Also, it's difficult to tell where your headers are and where your data starts.

 

[Jedi Master]

Are you open to using a macro? Also, it's difficult to tell where your headers are and where your data starts.

Sorry I cut off row numbers in a couple screenshots. Headers are in row 49, data starts on A50. I would have to see a macro work to know if I can use it or not.

 

[mumps]

If you want to try the macro, do the following:
-make sure that macros are enabled in Excel
-hold down the ALT key and press the F11 key to open the Visual Basic Editor
-click 'Insert' in the menu at the top
-click 'Module'
-copy/paste the macro into the empty window that appears
-press the F5 key to run the macro
-close the Visual Basic Editor to return to your sheet
There are easier ways to run macros such as clicking a button on your sheet or a letter of your choice on the keyboard. If you are interested in this approach, please let me know.

[CODE=vba]
Sub CombineData()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, ws As Worksheet
    Set desWS = Sheets("Quote Task Master")
    For Each ws In Sheets
        If ws.Name <> "Quote Task Master" Then
            With ws
                .Range("A49").AutoFilter Field:=1, Criteria1:="<>"
                .AutoFilter.Range.Offset(1).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
                .Range("A49").AutoFilter
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

[/CODE]

 

[Jedi Master]

If you want to try the macro, do the following:
-make sure that macros are enabled in Excel
-hold down the ALT key and press the F11 key to open the Visual Basic Editor
-click 'Insert' in the menu at the top
-click 'Module'
-copy/paste the macro into the empty window that appears
-press the F5 key to run the macro
-close the Visual Basic Editor to return to your sheet
There are easier ways to run macros such as clicking a button on your sheet or a letter of your choice on the keyboard. If you are interested in this approach, please let me know.

[CODE=vba]
Sub CombineData()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, ws As Worksheet
    Set desWS = Sheets("Quote Task Master")
    For Each ws In Sheets
        If ws.Name <> "Quote Task Master" Then
            With ws
                .Range("A49").AutoFilter Field:=1, Criteria1:="<>"
                .AutoFilter.Range.Offset(1).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
                .Range("A49").AutoFilter
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

[/CODE]

I dont think F5 is running the macro, just adjusts my volume?

 

[mumps]

Click on 'Run' in the menu at the top and then click on 'Run' in the drop down that appears.

 

[Jedi Master]

Click on 'Run' in the menu at the top and then click on 'Run' in the drop down that appears.

 

[mumps]

Delete the highlighted line.

 

[Jedi Master]

Delete the highlighted line.

Im sorry, this isnt working for me for some reason. It would probably be better if I could use a formula variation as posted initially.

 

[mumps]

The code worked properly on a dummy file. Could you upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.