I have a workbook ("A") with 81 tabs. I am looking to grab the text in cell
A50 in each of these tabs and have it "posted" on another workbook ("B") in a
cell corresponding to that tab's row. So if I have text such as "no exception
noted" on tab 1A-1, cell A50, (from Workbook A), I would like to have that
text copied on workbook B in a cell from a column named "summary of results"
and in the row that corresponds to the tab named 1A-1. I know how to do this
part for a single tab but because there are 81 tabs a way to do this for
every tab in a single formula (each tab has obviously a different tab name)?

Workbook B
tab ref summary of results
1a-1 no exception noted
1b-1 document not signed
1b-2
1c-1

The summary of results text comes from cell A50 in each of the tabs in
workbook A.

Hope I explain this clearly and someone out there can help.

Thanks,
pfa

I am not sure I understand you correct

Start with this macro that create the links for you to a cell in each sheet
http://www.rondebruin.nl/summary.htm


--

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


"pfa" wrote in message ...
I have a workbook ("A") with 81 tabs. I am looking to grab the text in cell
A50 in each of these tabs and have it "posted" on another workbook ("B") in a
cell corresponding to that tab's row. So if I have text such as "no exception
noted" on tab 1A-1, cell A50, (from Workbook A), I would like to have that
text copied on workbook B in a cell from a column named "summary of results"
and in the row that corresponds to the tab named 1A-1. I know how to do this
part for a single tab but because there are 81 tabs a way to do this for
every tab in a single formula (each tab has obviously a different tab name)?

Workbook B
tab ref summary of results
1a-1 no exception noted
1b-1 document not signed
1b-2
1c-1

The summary of results text comes from cell A50 in each of the tabs in
workbook A.

Hope I explain this clearly and someone out there can help.

Thanks,
pfa

I will attempt to clarify. To be specific, I have a workbook named "Summary
Master". In cell O7 of this workbook I have the following formula: ='[DPP Q2
2007 Leadsheets.xls]1A-1'!$A$50. (The DPP Q2 2007 Leadsheets workbook is
comprised of 81 tabs, named 1A-1, 1B-1, 1B-2, etc.)
I would like to have the text typed in A50 populate cell O7 of the Summary
Master workbook every time someone adds tezt to that cell in every one of
the 81 tabs on the DPP Q2 workbook.
Rather than copy the formula to the next cell (O8), editing the formula
above to call up the next tab reference name (i.e., changing the tab name to
1B-1 from 1A-1, for example), my question is if there is an easier way to do
this? It just seems a lot of work to edit the formula 81 times...plus there
are several other workbooks that I need to link to this summary workbook
using the very same concept.

I hope I helped clarify this problem for you.

"Ron de Bruin" wrote:

I am not sure I understand you correct

Start with this macro that create the links for you to a cell in each sheet
http://www.rondebruin.nl/summary.htm


--

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


"pfa" wrote in message ...
I have a workbook ("A") with 81 tabs. I am looking to grab the text in cell
A50 in each of these tabs and have it "posted" on another workbook ("B") in a
cell corresponding to that tab's row. So if I have text such as "no exception
noted" on tab 1A-1, cell A50, (from Workbook A), I would like to have that
text copied on workbook B in a cell from a column named "summary of results"
and in the row that corresponds to the tab named 1A-1. I know how to do this
part for a single tab but because there are 81 tabs a way to do this for
every tab in a single formula (each tab has obviously a different tab name)?

Workbook B
tab ref summary of results
1a-1 no exception noted
1b-1 document not signed
1b-2
1c-1

The summary of results text comes from cell A50 in each of the tabs in
workbook A.

Hope I explain this clearly and someone out there can help.

Thanks,
pfa

Hi pfa

I will make a example for you tomorrow after work
Bed time now for me



--

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


"pfa" wrote in message ...
I will attempt to clarify. To be specific, I have a workbook named "Summary
Master". In cell O7 of this workbook I have the following formula: ='[DPP Q2
2007 Leadsheets.xls]1A-1'!$A$50. (The DPP Q2 2007 Leadsheets workbook is
comprised of 81 tabs, named 1A-1, 1B-1, 1B-2, etc.)
I would like to have the text typed in A50 populate cell O7 of the Summary
Master workbook every time someone adds tezt to that cell in every one of
the 81 tabs on the DPP Q2 workbook.
Rather than copy the formula to the next cell (O8), editing the formula
above to call up the next tab reference name (i.e., changing the tab name to
1B-1 from 1A-1, for example), my question is if there is an easier way to do
this? It just seems a lot of work to edit the formula 81 times...plus there
are several other workbooks that I need to link to this summary workbook
using the very same concept.

I hope I helped clarify this problem for you.

"Ron de Bruin" wrote:

I am not sure I understand you correct

Start with this macro that create the links for you to a cell in each sheet
http://www.rondebruin.nl/summary.htm


--

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


"pfa" wrote in message ...
I have a workbook ("A") with 81 tabs. I am looking to grab the text in cell
A50 in each of these tabs and have it "posted" on another workbook ("B") in a
cell corresponding to that tab's row. So if I have text such as "no exception
noted" on tab 1A-1, cell A50, (from Workbook A), I would like to have that
text copied on workbook B in a cell from a column named "summary of results"
and in the row that corresponds to the tab named 1A-1. I know how to do this
part for a single tab but because there are 81 tabs a way to do this for
every tab in a single formula (each tab has obviously a different tab name)?

Workbook B
tab ref summary of results
1a-1 no exception noted
1b-1 document not signed
1b-2
1c-1

The summary of results text comes from cell A50 in each of the tabs in
workbook A.

Hope I explain this clearly and someone out there can help.

Thanks,
pfa

One way

You can copy this macro in a module in this workbook
DPP Q2 2007 Leadsheets.xls
After you run it you have a worksheet named "Summary-Sheet" with your 82 formulas

Copy the 82 formula cells
Open Summary Master.xls
Paste the cells (it will add the workbook name now to the formula)
Save the file
Delete the "Summary-Sheet" in DPP Q2 2007 Leadsheets.xls

Sub Summary_All_Worksheets_With_Formulas()
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Delete the sheet "Summary-Sheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Summary-Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Summary-Sheet"
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add
Newsh.Name = "Summary-Sheet"

'The links to the first sheet will start in row 2
RwNum = 1

For Each Sh In Basebook.Worksheets
If Sh.Name < Newsh.Name And Sh.Visible Then
ColNum = 1
RwNum = RwNum + 1
'Copy the sheet name in the A column
Newsh.Cells(RwNum, 1).Value = Sh.Name

For Each myCell In Sh.Range("A50") '<--Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(True, True)
Next myCell

End If
Next Sh

Newsh.UsedRange.Columns.AutoFit

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub


--

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


"Ron de Bruin" wrote in message ...
Hi pfa

I will make a example for you tomorrow after work
Bed time now for me



--

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


"pfa" wrote in message ...
I will attempt to clarify. To be specific, I have a workbook named "Summary
Master". In cell O7 of this workbook I have the following formula: ='[DPP Q2
2007 Leadsheets.xls]1A-1'!$A$50. (The DPP Q2 2007 Leadsheets workbook is
comprised of 81 tabs, named 1A-1, 1B-1, 1B-2, etc.)
I would like to have the text typed in A50 populate cell O7 of the Summary
Master workbook every time someone adds tezt to that cell in every one of
the 81 tabs on the DPP Q2 workbook.
Rather than copy the formula to the next cell (O8), editing the formula
above to call up the next tab reference name (i.e., changing the tab name to
1B-1 from 1A-1, for example), my question is if there is an easier way to do
this? It just seems a lot of work to edit the formula 81 times...plus there
are several other workbooks that I need to link to this summary workbook
using the very same concept.

I hope I helped clarify this problem for you.

"Ron de Bruin" wrote:

I am not sure I understand you correct

Start with this macro that create the links for you to a cell in each sheet
http://www.rondebruin.nl/summary.htm


--

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


"pfa" wrote in message ...
I have a workbook ("A") with 81 tabs. I am looking to grab the text in cell
A50 in each of these tabs and have it "posted" on another workbook ("B") in a
cell corresponding to that tab's row. So if I have text such as "no exception
noted" on tab 1A-1, cell A50, (from Workbook A), I would like to have that
text copied on workbook B in a cell from a column named "summary of results"
and in the row that corresponds to the tab named 1A-1. I know how to do this
part for a single tab but because there are 81 tabs a way to do this for
every tab in a single formula (each tab has obviously a different tab name)?

Workbook B
tab ref summary of results
1a-1 no exception noted
1b-1 document not signed
1b-2
1c-1

The summary of results text comes from cell A50 in each of the tabs in
workbook A.

Hope I explain this clearly and someone out there can help.

Thanks,
pfa

Ron,

It worked to perfection. Thanks for being so helpful! I will add a caveat to
this problem tomorrow that may or may not be possible to address.

For now, many thanks!


"Ron de Bruin" wrote:

One way

You can copy this macro in a module in this workbook
DPP Q2 2007 Leadsheets.xls
After you run it you have a worksheet named "Summary-Sheet" with your 82 formulas

Copy the 82 formula cells
Open Summary Master.xls
Paste the cells (it will add the workbook name now to the formula)
Save the file
Delete the "Summary-Sheet" in DPP Q2 2007 Leadsheets.xls

Sub Summary_All_Worksheets_With_Formulas()
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Delete the sheet "Summary-Sheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Summary-Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Summary-Sheet"
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add
Newsh.Name = "Summary-Sheet"

'The links to the first sheet will start in row 2
RwNum = 1

For Each Sh In Basebook.Worksheets
If Sh.Name < Newsh.Name And Sh.Visible Then
ColNum = 1
RwNum = RwNum + 1
'Copy the sheet name in the A column
Newsh.Cells(RwNum, 1).Value = Sh.Name

For Each myCell In Sh.Range("A50") '<--Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(True, True)
Next myCell

End If
Next Sh

Newsh.UsedRange.Columns.AutoFit

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub


--

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


"Ron de Bruin" wrote in message ...
Hi pfa

I will make a example for you tomorrow after work
Bed time now for me



--

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


"pfa" wrote in message ...
I will attempt to clarify. To be specific, I have a workbook named "Summary
Master". In cell O7 of this workbook I have the following formula: ='[DPP Q2
2007 Leadsheets.xls]1A-1'!$A$50. (The DPP Q2 2007 Leadsheets workbook is
comprised of 81 tabs, named 1A-1, 1B-1, 1B-2, etc.)
I would like to have the text typed in A50 populate cell O7 of the Summary
Master workbook every time someone adds tezt to that cell in every one of
the 81 tabs on the DPP Q2 workbook.
Rather than copy the formula to the next cell (O8), editing the formula
above to call up the next tab reference name (i.e., changing the tab name to
1B-1 from 1A-1, for example), my question is if there is an easier way to do
this? It just seems a lot of work to edit the formula 81 times...plus there
are several other workbooks that I need to link to this summary workbook
using the very same concept.

I hope I helped clarify this problem for you.

"Ron de Bruin" wrote:

I am not sure I understand you correct

Start with this macro that create the links for you to a cell in each sheet
http://www.rondebruin.nl/summary.htm


--

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


"pfa" wrote in message ...
I have a workbook ("A") with 81 tabs. I am looking to grab the text in cell
A50 in each of these tabs and have it "posted" on another workbook ("B") in a
cell corresponding to that tab's row. So if I have text such as "no exception
noted" on tab 1A-1, cell A50, (from Workbook A), I would like to have that
text copied on workbook B in a cell from a column named "summary of results"
and in the row that corresponds to the tab named 1A-1. I know how to do this
part for a single tab but because there are 81 tabs a way to do this for
every tab in a single formula (each tab has obviously a different tab name)?

Workbook B
tab ref summary of results
1a-1 no exception noted
1b-1 document not signed
1b-2
1c-1

The summary of results text comes from cell A50 in each of the tabs in
workbook A.

Hope I explain this clearly and someone out there can help.

Thanks,
pfa