Consolidating Information

[donh]

Hi there hope you can help.

Sorry long explanation in the hope it helps.

One of the projects I've been working in is a NON TABULAR form which
is split into about 6 worksheets each covering a different part of the
information required. Cells normally have an explanatory heading on
the left with an input cell to the right, but this is not always the
case.

The form has been designed to look nice and straightforward for
the person doing the data input. Each workbook provides information
on a single location.

Its now been decided that this would be good to link into an access
database for testing prior to moving into a SQL database :-( So all
information on premises are linked and searchable.

So far we probably have around 150 workbooks each covering a seperate
location (premise). This is far too much information to dump and
start again.

I need to be able (I think), gather the information from the various
cell addresses over the 6 or so worksheets and compile a new worksheet
where this could be setout into a tabular table format ready for
exporting.

Question 1 am I correct in thinking that would be a good place to
start?

Question 2 I need to be able to replicate this tabular worksheet in
the rest of the workbooks! is a formula such as =INDIRECT("Sheet1!
E15") etc the best way of getting around cells keeping the reference
to the originating workbook.

And finally Question 3 if this is the right way to go, is there then a
peice of VBA which will add each tab table to a master workbook with
info being added to next blank line.

Sorry if thats too much in one go.

Thanks in anticipation



Don H

 

[donh]

Hi,
Following on from my last post I have now created a row of data
(currently B2A2) using for example =INDIRECT("'Part A Site
Assessment'!B153") to gather cell data. But could really do with help
on a piece of VBA to copy this to a blank row on a Master Workbook.

As always any help would be appreciated.

Regards

DonH