Possiblity Question

snowcloud72

New Member
Joined
Jan 23, 2017
Messages
6
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
what sort of errors do you expect, do they occur only in specific columns
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top