B
BabyMc
I am trying to set up a formatted report, using Excel 2003, which
extracts data from a table in another workbook. However my request is to
find out whether there is a way to include lines on the report where
there is a matching entry in the table of data. The attachment and
following notes, I hope, might explain further.
The worksheet Group Data (which is going to be in the other workbook)
contains a table of information which is used to extract data within the
other sheets (e.g. in worksheet 306300 - the report).
For each instance of Sub code, against that cost centre in the Group
Data table, there needs to be a line included in the report (this is so
that the report balances back to the data.
The worksheet 306300 (which is a cost centre) currently contains around
a dozen 'Sub' codes (in column C), which are those required to extract
the information for the report. However, the issue is that the Group
Data table will expand each month with new Sub codes, and therefore
lines of data.
I would like to find a way of adding the relevant lines to the report
whenever that line is included in the Group Data table (and to exclude
the line if it is not in the Group Data table).
The only ways I can think of to do this is to either: -
1) Include a line, in the report, for every known Sub code possible (up
to 10,000) and hiding (possibly by means of filtering) those which have
no data. However I can't see that this can be practical as this is, say
10,000 lookups - multiplied by about 6 (columns with data on the
report), multiplied by the number of cost centres in the workbook (which
can be anything from 1 to 30). I have tried to use this method elsewhere
and calculation times are enormous.
2) Manually review each cost centre, in turn, each month. Where the
report does not balance, review the Group Data table for new Sub codes
not previously included, add a row in the report and include the new Sub
codes there.
Option 2, although time-consuming (there are currently around 200 - 300
cost centres across the organisation) seems the only method available.
Thanks, in advance, for any help.
+-------------------------------------------------------------------+
|Filename: MH Purchasing (for upload).xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=158|
+-------------------------------------------------------------------+
extracts data from a table in another workbook. However my request is to
find out whether there is a way to include lines on the report where
there is a matching entry in the table of data. The attachment and
following notes, I hope, might explain further.
The worksheet Group Data (which is going to be in the other workbook)
contains a table of information which is used to extract data within the
other sheets (e.g. in worksheet 306300 - the report).
For each instance of Sub code, against that cost centre in the Group
Data table, there needs to be a line included in the report (this is so
that the report balances back to the data.
The worksheet 306300 (which is a cost centre) currently contains around
a dozen 'Sub' codes (in column C), which are those required to extract
the information for the report. However, the issue is that the Group
Data table will expand each month with new Sub codes, and therefore
lines of data.
I would like to find a way of adding the relevant lines to the report
whenever that line is included in the Group Data table (and to exclude
the line if it is not in the Group Data table).
The only ways I can think of to do this is to either: -
1) Include a line, in the report, for every known Sub code possible (up
to 10,000) and hiding (possibly by means of filtering) those which have
no data. However I can't see that this can be practical as this is, say
10,000 lookups - multiplied by about 6 (columns with data on the
report), multiplied by the number of cost centres in the workbook (which
can be anything from 1 to 30). I have tried to use this method elsewhere
and calculation times are enormous.
2) Manually review each cost centre, in turn, each month. Where the
report does not balance, review the Group Data table for new Sub codes
not previously included, add a row in the report and include the new Sub
codes there.
Option 2, although time-consuming (there are currently around 200 - 300
cost centres across the organisation) seems the only method available.
Thanks, in advance, for any help.
+-------------------------------------------------------------------+
|Filename: MH Purchasing (for upload).xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=158|
+-------------------------------------------------------------------+