LINKING MAIN SHEET WITH MULTIPLE SUMMARY SHEETS

[SSJ]

Hello there!

I need guidance in coming up with an easy way to link my MAIN SHEET tab to various project tabs. The current way I am accomplishing this task is quite tedious.

1) Data in the MAIN SHEET is not in a list form. The MAIN SHEET is a complex work of art of download links and formulas. The MAIN SHEET contains data on multiple projects. Each project has varying numbers of work orders and of course more are added as needed. Some of the fields/columns for each project/work orders are: in the area of actual cost (labour hours, labour cost, & material), additional cost (labour hours, labour cost, & material), Forecast (labour hours, labour cost, & material), amounting to almost 30 columns.

2) Each project along with its work orders are listed in its own project tab. A particular project tab will list all the work orders from the MAIN SHEET with it related fields/column of actual cost, additional cost, forecasts, etc.

3) I have inherited this spreadsheet and the way it was done current, which i want to change is as follows:

i) I open a tab for a project. The lay out of the project sheets are identical to the MAIN SHEET, however, the number of work order varyies. The number of work orders are in hundreds.
ii) List all the work orders numbers and description
iii) Manually link each and every field (there are 30) of the work order (there are 100s) with the corresponding cell in the MAIN SHEET.

As you can see it is pretty tedious. I need ideas how it can be made easy.

Thanks
SJ

 

[Don Guillett]

Sounds like a project for a professional
Pls dont post in html
--
Don Guillett
SalesAid Software
(e-mail address removed)
Hello there!

I need guidance in coming up with an easy way to link my MAIN SHEET tab to
various project tabs. The current way I am accomplishing this task is quite
tedious.

1) Data in the MAIN SHEET is not in a list form. The MAIN SHEET is a complex
work of art of download links and formulas. The MAIN SHEET contains data on
multiple projects. Each project has varying numbers of work orders and of
course more are added as needed. Some of the fields/columns for each
project/work orders are: in the area of actual cost (labour hours, labour
cost, & material), additional cost (labour hours, labour cost, & material),
Forecast (labour hours, labour cost, & material), amounting to almost 30
columns.

2) Each project along with its work orders are listed in its own project
tab. A particular project tab will list all the work orders from the MAIN
SHEET with it related fields/column of actual cost, additional cost,
forecasts, etc.

3) I have inherited this spreadsheet and the way it was done current, which
i want to change is as follows:

i) I open a tab for a project. The lay out of the project sheets are
identical to the MAIN SHEET, however, the number of work order varyies. The
number of work orders are in hundreds.
ii) List all the work orders numbers and description
iii) Manually link each and every field (there are 30) of the work order
(there are 100s) with the corresponding cell in the MAIN SHEET.

As you can see it is pretty tedious. I need ideas how it can be made easy.

Thanks
SJ

 

[Harlan Grove]

3) I have inherited this spreadsheet and the way it was done current,
which i want to change is as follows:

i) I open a tab for a project. The lay out of the project sheets are
identical to the MAIN SHEET, however, the number of work order varyies.
The number of work orders are in hundreds.
ii) List all the work orders numbers and description
iii) Manually link each and every field (there are 30) of the work
order (there are 100s) with the corresponding cell in the MAIN SHEET.

....

You could use a macro to do this. Easiest to start by using the Macro
Recorder to record the steps needed to do this for one one project
worksheet. Then post the recorded macro and others here could help
generalize it to loop through all worksheets other than MAIN SHEET.