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

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

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


--

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


"John13" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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
>

On Feb 15, 12:48 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
> Try this Johnhttp://www.rondebruin.nl/summary2.htm
>
> Or if you want to copyhttp://www.rondebruin.nl/copy3.htm
>
> --
>
> Regards Ron de Bruinhttp://www.rondebruin.nl/tips.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

>

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


"John13" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> On Feb 15, 12:48 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
>> Try this Johnhttp://www.rondebruin.nl/summary2.htm
>>
>> Or if you want to copyhttp://www.rondebruin.nl/copy3.htm
>>
>> --
>>
>> Regards Ron de Bruinhttp://www.rondebruin.nl/tips.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
>
>>
>

On Feb 16, 3:03 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
> 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 Bruinhttp://www.rondebruin.nl/tips.htm
>
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

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

Try it


--

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


"John13" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> On Feb 16, 3:03 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
>> 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 Bruinhttp://www.rondebruin.nl/tips.htm
>>
> 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
>
>
>