Possiblity Question

[snowcloud72]

Hi,

I have a workbook that I am working on currently, that has a summary table I am needing help on. I am not sure if it is possible, but thought I would ask here to see if anyone had any ideas. My workbook is a year running total, with a summary sheet that identifies errors. I need a formula that will analyze a row of data, as there are possibility of 5 errors per row. IF it finds an error, it then records certain data elements on the summary sheet. However, if there are no errors in that row, then no data should be entered and the next row should be analyzed. Any help on this would be greatly appreciated.

Thanks,
Tracey

 

[mole999]

what sort of errors do you expect, do they occur only in specific columns

 

[snowcloud72]

Hi,
There are 20 columns of data. The errors are ones the analyst assigns when reviewing the data. So if there is an error in that row of data, we need to move it to the summary tab.

 

[snowcloud72]

This is my summary sheet. If there are values entered in the worksheet for "errors", I want these fields to auto-populate.[TABLE="width: 724"]
<colgroup><col><col span="10"></colgroup><tbody>[TR]
[TD="colspan: 2"]Business Analyst:[/TD]
[TD="colspan: 2"]#REF![/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 2"]Date of Review:[/TD]
[TD="colspan: 2"]1/23/2017[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Clinic Reviewed:[/TD]
[TD="colspan: 2"][Clinic][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Month of Data Reviewed: [/TD]
[TD]JAN[/TD]
[/TR]
[TR]
[TD="colspan: 11"]Audit Results (Data from QA Tool Template)[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="colspan: 5"]Number of Documents Reviewed[/TD]
[TD="colspan: 2"]Total Errors %[/TD]
[TD="colspan: 2"]Total Minors %[/TD]
[TD="colspan: 2"]Total Majors %[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD] [/TD]
[TD="colspan: 2"]0.00%[/TD]
[TD="colspan: 2"]0.00%[/TD]
[TD="colspan: 2"]0.00%[/TD]
[/TR]
[TR]
[TD="colspan: 11"]Summary of Findings[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Major/Minor/Comment:[/TD]
[TD] [/TD]
[TD="colspan: 7"] [/TD]
[/TR]
[TR]
[TD]Type:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]Encounter:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]Scanned:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]Indexed:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]QA:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]Page Count:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Major/Minor/Comment:[/TD]
[TD] [/TD]
[TD="colspan: 7"] [/TD]
[/TR]
[TR]
[TD]Type:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]Encounter:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]Scanned:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]Indexed:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]QA:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]Page Count:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Major/Minor/Comment:[/TD]
[TD] [/TD]
[TD="colspan: 7"] [/TD]
[/TR]
[TR]
[TD]Type:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]Encounter:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]Scanned:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]Indexed:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]QA:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]Page Count:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Major/Minor/Comment:[/TD]
[TD] [/TD]
[TD="colspan: 7"] [/TD]
[/TR]
[TR]
[TD]Type:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]Encounter:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]Scanned:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]Indexed:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]QA:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]Page Count:[/TD]
[TD="colspan: 3"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[snowcloud72]

Below is the Macro that I have created to help fill my summary sheet. Because of my limited skills, I am thinking I will need to do this for each month so that I can capture this data from each worksheet on the appropriate sheet. But am hoping you might have a suggestion on how I could do this better. It has been very time consuming just getting to this point, and I have about 4 more pages that I need to record for each month before I am finished. Any help would be appreciated.

Sub summary()
'
' summary Macro
'


'
Sheets("Jan").Select
Range("I3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Review Summary").Select
ActiveSheet.Paste
Sheets("Jan").Select
Range("J3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Review Summary").Select
Range("B12:D12").Select
ActiveSheet.Paste
Sheets("Jan").Select
Range("K3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Review Summary").Select
Range("B13:D13").Select
ActiveSheet.Paste
Sheets("Jan").Select
Range("L3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Review Summary").Select
Range("B14:D14").Select
ActiveSheet.Paste
Sheets("Jan").Select
Range("M3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Review Summary").Select
Range("B15:D15").Select
ActiveSheet.Paste
Sheets("Jan").Select
Range("T3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Review Summary").Select
Range("E10:K15").Select
ActiveSheet.Paste
Sheets("Jan").Select
Range("I4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Review Summary").Select
Range("B19:D19").Select
ActiveSheet.Paste
Sheets("Jan").Select
Range("J4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Review Summary").Select
Range("B20:D20").Select
ActiveSheet.Paste
Sheets("Jan").Select
Range("K4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Review Summary").Select
Range("B21:D21").Select
ActiveSheet.Paste
Sheets("Jan").Select
Range("L4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Review Summary").Select
Range("B22:D22").Select
ActiveSheet.Paste
Sheets("Jan").Select
Range("M4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Review Summary").Select
Range("B23:D23").Select
ActiveSheet.Paste
Sheets("Jan").Select
Range("T4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Review Summary").Select
Range("E18:K23").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=19
Sheets("Jan").Select
Range("I5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Review Summary").Select
Range("B26:D26").Select
ActiveSheet.Paste
Sheets("Jan").Select
Range("J5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Review Summary").Select
Range("B27:D27").Select
ActiveSheet.Paste
Sheets("Jan").Select
Range("K5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Review Summary").Select
Range("B28:D28").Select
ActiveSheet.Paste
Sheets("Jan").Select
Range("L5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Review Summary").Select
Range("B29:D29").Select
ActiveSheet.Paste
Sheets("Jan").Select
Range("M5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Review Summary").Select
Range("B30:D30").Select
ActiveSheet.Paste
Sheets("Jan").Select
Range("T5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Review Summary").Select
Range("E33:K38").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=0
End Sub