You could use a macro like this - just change the specifics, of course. The
code goes into the summary workbook, which has a sheet name "Summary Sheet"
with dates down column A. The data files have the key date in cell B1 of
the default worksheet. And then the values in cellsC1, F16, H3, I9, and B12
are transfered to columns B, C, D, E, and F of the summary sheet.

Sub PullData()
Dim myR As Long
Dim mySR As Range
Dim myD As Range

Workbooks.Open Application.GetOpenFilename(, , "Select the workbook")

Set myD = Range("B1")
Set mySR = ThisWorkbook.Worksheets("Summary Sheet").Range("A:A")

myR = WorksheetFunction.Match(CDbl(myD.Value), mySR, False)
With ThisWorkbook.Worksheets("Summary Sheet")
..Range("B" & myR).Formula = Range("C1").Value
..Range("C" & myR).Formula = Range("F16").Value
..Range("D" & myR).Formula = Range("H3").Value
..Range("E" & myR).Formula = Range("I9").Value
..Range("F" & myR).Formula = Range("B12").Value
End With
End Sub



HTH,
Bernie
MS Excel MVP


"danish404" wrote in message
...
Hi

I have a daily template that is completed by some team members, this has
to
be in a format that is easy to read and use for people not used to
spreadsheets.

However, I need to transfer the data to the main spreadsheet, in a
different
format so that it can be used to generate reports. I want to use excel as
those that will be using the reports don't need to be trained.

The input sheet will have a unique date on the form, this is also already
present in the main workbook, and will act as the key to link the data
extracted to the correct location.

The data is in the form of an easy questionnaire, but needs to be
translated
into a single row of information for each date. The format of the source
and
destination will not change, but the data will need to be placed against
the
date row of the destination sheet.

So the question is, how do I automatically transfer the information from
the
first (remembering that there will be around 20 of these each month) to
the
second (only ever one)?