Combining Spreadsheets On An On-going Basis

[John13]

We have been using for 5 years now a form in Excel called Problem
Report. Every report is the same. The report number is in cell K3.
The defect code is in cell L20 and so on. I have to summarize some of
the fields and want to end up with a report like the following:

Report# Date Defect Code Etc.
07-001 1/2/07 23 XXX
07-002 1/3/07 22 YYY
07-003 1/3/07 18 ZZZ

Can I bring all the reports into a workbook as tabs and have the
workbook automatically update a summary tab each time a tab is added
(adding it as a row to the summary)?

There are about 45 specific cells on a Problem Report I want to work
with, so doing it manually is out. I know this sounds more like an
Access solution, but we have no Access guru's around and everything is
in Excel. Help please!

John

 

[Ron de Bruin]

Try this John
http://www.rondebruin.nl/summary2.htm

Or if you want to copy
http://www.rondebruin.nl/copy3.htm

 

[John13]

Try this Johnhttp://www.rondebruin.nl/summary2.htm

Or if you want to copyhttp://www.rondebruin.nl/copy3.htm

Thank you for the reply. I went to the referenced sites and since I
have very little experience with the code side of Excel I'm not having
any luck. I looked at it using formulas and hit a wall.

In the formula: ='[07-004.xls]Blank Form'!K3 the K3 is a cell in
the spreadsheet 07-004.xls on tab Blank Form.

Can I enter this formula where the K3 is a variable that I can change
by the value of a separate cell?

I am trying to get an easy way to get the formula in a series of
columns where each column has its own variable (column C the variable
location is K3, column D it is K8, ect.).

I tried ='[07-004.xls]Blank Form'!&C1 and
='[07-004.xls]Blank Form'!&"C1" to no avail.

C1 is where the reference variable resides (as well as D1, E1, etc.).
Any suggestions anyone?

John

 

[Ron de Bruin]

Hi John

We try Example 1

Open a new workbook and paste the macro in a module in this workbook
Save this workbook outside the folder with the reports

Change this two lines with your sheet name and range

ShName = "Sheet1" '<---- Change
Set Rng = Range("A1,D5:E5,Z10") '<---- Change

Fill in the sheet name where the date is in all your report workbooks
Add all your 45 cells in the Rng string

Then run the macro

Browse the folder with your files in the dialog that poup and select all files (ctrl-a for all files in the folder)
Or use the ctrl key and shift key to select files.
OK

Now you see the formula links in a new workbook with one line for each report workbook


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Try this Johnhttp://www.rondebruin.nl/summary2.htm

Or if you want to copyhttp://www.rondebruin.nl/copy3.htm

Thank you for the reply. I went to the referenced sites and since I
have very little experience with the code side of Excel I'm not having
any luck. I looked at it using formulas and hit a wall.

In the formula: ='[07-004.xls]Blank Form'!K3 the K3 is a cell in
the spreadsheet 07-004.xls on tab Blank Form.

Can I enter this formula where the K3 is a variable that I can change
by the value of a separate cell?

I am trying to get an easy way to get the formula in a series of
columns where each column has its own variable (column C the variable
location is K3, column D it is K8, ect.).

I tried ='[07-004.xls]Blank Form'!&C1 and
='[07-004.xls]Blank Form'!&"C1" to no avail.

C1 is where the reference variable resides (as well as D1, E1, etc.).
Any suggestions anyone?

John

 

[John13]

Hi John

We try Example 1

Open a new workbook and paste the macro in a module in this workbook
Save this workbook outside the folder with the reports

Change this two lines with your sheet name and range

ShName = "Sheet1" '<---- Change
Set Rng = Range("A1,D5:E5,Z10") '<---- Change

Fill in the sheet name where the date is in all your report workbooks
Add all your 45 cells in the Rng string

Then run the macro

Browse the folder with your files in the dialog that poup and select all files (ctrl-a for all files in the folder)
Or use the ctrl key and shift key to select files.
OK

Now you see the formula links in a new workbook with one line for each report workbook

Ron,
I have a folder on the network drive with nothing but report
spreadsheets. File names are 07-001.xls, 07-002.xls, etc. and each
file has one tab in it named Blank Form. I create a new spreadsheet
away from that folder. The question I have is:

ShName = "Sheet1" '<---- Change
Set Rng = Range("A1,D5:E5,Z10") '<---- Change

Are you saying change sheet name to "Blank Form" (report workbook
name) or "2007 Summary" (new file summary tab with the code)?
The range, is the range of the summary or the range of the report
workbooks?

Fill in the sheet name where the date is in all your report workbooks (the date is K8 in all report workbooks)
Add all your 45 cells in the Rng string (In what format?)

I really appreciate your help and patience with this project.

John

 

[Ron de Bruin]

The sheet name is the name of the sheet in all your report workbooks, Blank Form in your case.

Try it