Update Count from Different worksheet

[Abhijeet]

Hi
I have Data in Excel i want update count with criteria. In excel file 5 sheets from that Trust Numbers in Column C & Action Taken in Column F from that i want trust wise counts of Confirmation, Query Re Query & Error query.

Same Data Trust wise in every excel files & all files are saved in 1 folder.Same trust wise counts of Confirmation, Query Re Query & Error query. from each file i want to update .
Expected result i show in Attach file

 

[Abhijeet]

These are trust wise files & all these files are saved in 1 folder

 

[Luke M]

The first step will be to merge all the data together. You can use the RDBMerge Add-in to easily accomplish this.
http://www.rondebruin.nl/win/addins/rdbmerge.htm

Once the data is all in one workbook, you'll be able to setup the tables you have in Main Data New using some COUNTIFS formulas.

 

[Abhijeet]

No i want only update the counts single files are update every 20 min & these are 30 to 40 files so its not possible to run macro every time for merge this

 

[Luke M]

If you only need a basic count, then write a COUNTIF function in each of your tables, pointing to correct workbook like so.

Will take a little bit to set them all up, but once it's done the formulas will do the work for you. Just need to Update values when desired.

 

[Abhijeet]

Yes Luke M i use Countifs Formula but when that file is open then that work other wise get Value Error Then i use sum+If formula but that will take too much time

 

[Luke M]

Then you are back to original point, that you need to merge the data. Frankly, the real source of problem is poor spreadsheet design. If you can change your system so that it puts all data in one workbook, with raw data on one sheet, data analysis becomes much easier.
http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm#Layout

So, your options are to make-do with the Merge tool, or change the overall design.

 

[Abhijeet]

Design to change it is not possible & run the merge macro every time is not possible
These are trust files every trust update these files & changes are saved in round about every 20 mins so every files changes i want to reflect so its not possible to run these merge macro

 

[Luke M]

Then I'm afraid you are at an endpass, as "somehow" you need to get to the data.

 

[Abhijeet]

Hi
I have this macro but i want sheet wise update the count of each file

Sub SeeTheValue()

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim resultQ As Long
Dim resultC As Long
Dim destRangeQ As Range
Dim destRangeC As Range
Dim nextRow As Long
Dim lr As Long
Dim DestSheet As Worksheet

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Set DestSheet = ThisWorkbook.Worksheets("Sheet1")
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With


NextCode:
myPath = myPath
If myPath = "" Then Exit Sub
myExtension = "*.xlsx"
myFile = Dir(myPath & myExtension)

nextRow = 2
Do While myFile <> ""

Set wb = Workbooks.Open(FileName:=myPath & myFile)
lr = wb.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
DestSheet.Range("A" & nextRow).Value = Left(myFile, InStr(1, myFile, ".xlsx", vbTextCompare) - 1)
For i = 2 To lr
If wb.ActiveSheet.Cells(i, 6) = "Query" Then
resultQ = resultQ + 1
Else
If wb.ActiveSheet.Cells(i, 6) = "Confirmation" Then
resultC = resultC + 1
End If
End If
Next i

Set destRangeQ = DestSheet.Range("B" & nextRow)
Set destRangeC = DestSheet.Range("C" & nextRow)

destRangeQ = resultQ
destRangeC = resultC
nextRow = nextRow + destRangeQ.Rows.Count
resultQ = 0
resultC = 0


wb.Close SaveChanges:=True


myFile = Dir
Loop



Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Abhijeet]

This macro gives only active sheet data not identify This is Travel Salary Absence data is this so please give me macro

 

[Abhijeet]

Hi Luke M please look this macro & give me macro according with my data

 

[Abhijeet]

Hi
I have this macro but i want sheet wise update the count of each file

Sub SeeTheValue()

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim resultQ As Long
Dim resultC As Long
Dim destRangeQ As Range
Dim destRangeC As Range
Dim nextRow As Long
Dim lr As Long
Dim DestSheet As Worksheet

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Set DestSheet = ThisWorkbook.Worksheets("Sheet1")
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With


NextCode:
myPath = myPath
If myPath = "" Then Exit Sub
myExtension = "*.xlsx"
myFile = Dir(myPath & myExtension)

nextRow = 2
Do While myFile <> ""

Set wb = Workbooks.Open(FileName:=myPath & myFile)
lr = wb.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
DestSheet.Range("A" & nextRow).Value = Left(myFile, InStr(1, myFile, ".xlsx", vbTextCompare) - 1)
For i = 2 To lr
If wb.ActiveSheet.Cells(i, 6) = "Query" Then
resultQ = resultQ + 1
Else
If wb.ActiveSheet.Cells(i, 6) = "Confirmation" Then
resultC = resultC + 1
End If
End If
Next i

Set destRangeQ = DestSheet.Range("B" & nextRow)
Set destRangeC = DestSheet.Range("C" & nextRow)

destRangeQ = resultQ
destRangeC = resultC
nextRow = nextRow + destRangeQ.Rows.Count
resultQ = 0
resultC = 0


wb.Close SaveChanges:=True


myFile = Dir
Loop



Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub

Please tell me as per my data i have upload sample files here
http://forum.chandoo.org/threads/update-count-from-different-worksheet.19566/#post-117974