Accessing same cell in multiple worksbooks

[Gopi]

Hi,

I am currentl yworking on something like a feedback from. The form is
devised in Excel and the answers have been given in a Drop Down list. SO, the
person giving feedback needs to select only 'Yes', 'No', or 'N/A'. Now I have
1000's of tehse sheets in batches of 40 from each region.

I need to collate a summary for each region. Is there a way by which I can
acess these worksheets region wise and collect (sum up) the 'Yes', 'No' and
'N/A' for each region. These are in cell 'I10' in all the sheets.

Also, each region has 'n' number of feedback forms where 'n' is not known.
Can we stil lcollate a summary?

Any help in this issue wil lbe deeply appreciated.

Thanks in anticipation of all your help.

Regards
Gopi

[Bernie Deitrick]

Gopi,

Copy the macro below into a blank workbook, then, when you run it, select the files that you want to
summarize when prompted.

This assumes that all the sheets have the same name, and that the activesheet of any of the
workbooks will be the sheet of interest. Of course, if that is wrong, you can easily change that
part of the code.

HTH,
Bernie
MS Excel MVP


Sub CreateLinksToMulitpleFiles()
Dim MyFormula As String
Dim myCount As Long
Dim LookIn As String
Dim FileArray As Variant
Dim myShtName As String
Dim i As Integer

myCount = Cells(Rows.Count, 1).End(xlUp)(2).Row
FileArray = Application.GetOpenFilename(MultiSelect:=True)

If IsArray(FileArray) Then
Workbooks.Open FileArray(LBound(FileArray))
LookIn = ActiveWorkbook.Path
myShtName = ActiveSheet.Name
ActiveWorkbook.Close False
For i = LBound(FileArray) To UBound(FileArray)
'Generate myFormula through string manipulation
MyFormula = "='" & LookIn & "\[" & _
Replace(FileArray(i), LookIn & "\", "") _
& "]" & myShtName & "'!I10"
'Set cell formula
Cells(myCount, 1).Formula = MyFormula
myCount = myCount + 1
Next i
End If

End Sub





"Gopi" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I am currentl yworking on something like a feedback from. The form is
> devised in Excel and the answers have been given in a Drop Down list. SO, the
> person giving feedback needs to select only 'Yes', 'No', or 'N/A'. Now I have
> 1000's of tehse sheets in batches of 40 from each region.
>
> I need to collate a summary for each region. Is there a way by which I can
> acess these worksheets region wise and collect (sum up) the 'Yes', 'No' and
> 'N/A' for each region. These are in cell 'I10' in all the sheets.
>
> Also, each region has 'n' number of feedback forms where 'n' is not known.
> Can we stil lcollate a summary?
>
> Any help in this issue wil lbe deeply appreciated.
>
> Thanks in anticipation of all your help.
>
> Regards
> Gopi