Hi,

I am working on a model where I integrate Microsoft Excel and Microsoft
Project. There is a constantly changing resource data in Microsoft project. I
have written a VBA code in Project to export the required data into an Excel
sheet. The exported data does not comprise of a standard column set; in other
words, the number of columns varies according to the necessity. There is a
cell containing the total value at the bottom of the last column. This cell
value should be linked to a cell in another sheet. Since there is
inconsistency in the number of imported columns, the location of this cell
keeps shifting making it difficult to link to another sheet. Can anybody
think of any suggestions? Any help would be really appreciated.

Thanks
KT

I can think of a few possible solutions: First, if the layout of your
Project data on the sheet meets a few simple criteria you may be able to use
a simple worksheet formula: For example, if the data is in a table of
continuous rows + columns with no blanks (at least in the header row and the
first column) and if there is nothing else on the sheet, you could use the
COUNTA function to count the rows and columns, and this could be the formula
for your total value (use the actual sheet name instead of ProjectDataSheet,
of course)
=OFFSET(ProjectDataSheet!A1,COUNTA(ProjectDataShee t!$A:$A)-1,COUNTA(ProjectDataSheet!$1:$1)-1)
Another solution would rely on modifying the VBA in Project to somehow count
the columns and rows of data (assuming that can be done) and to store those
counts in specified cells in your spreadsheet. Then you can use the same
type of formula I show above to find the cell - just use the stored values
instead of the COUNTA functions.
Finally, if there is an identifying column header, e.g. "TOTAL", and an
identifying row label, e.g. "GRAND TOTAL" that will ALWAYS be there - and
NEVER anywhere else - that identify the last column and last row, you could
use the MATCH function to find those labels and then use the OFFSET to find
the cell, for example:
=OFFSET(ProjectDataSheet!A1,MATCH("GRAND
TOTAL",ProjectDataSheet!$A:$A,0)-1,MATCH("TOTAL",ProjectDataSheet!$1:$1,0)-1)
--
- K Dales


"KT" wrote:

Hi,

I am working on a model where I integrate Microsoft Excel and Microsoft
Project. There is a constantly changing resource data in Microsoft project. I
have written a VBA code in Project to export the required data into an Excel
sheet. The exported data does not comprise of a standard column set; in other
words, the number of columns varies according to the necessity. There is a
cell containing the total value at the bottom of the last column. This cell
value should be linked to a cell in another sheet. Since there is
inconsistency in the number of imported columns, the location of this cell
keeps shifting making it difficult to link to another sheet. Can anybody
think of any suggestions? Any help would be really appreciated.

Thanks
KT