First thank you again to 'Excelent' your suggestion for coloring rows was
spot on.
Now a new question.
I have several worksheets each containing similar information about
different clients and once a fortnight I am expected to send a report on all
activity. To do this I need to collate the information on a daily basis from
each worksheet then present it in the report as a total of all the
worksheets. At present I am doing this by placing formulae on the worksheets
to search out information according to date then add the columns of
information either as a sum or countif equations then gathering and summing
on the report page before actually placing the result in the appropriate
place on the report.
I know there must be a way of doing the same using visual basic but I am
nowhere near clever enough. Does anyone have any ideas? At present I am using
in excess of 52mb and am afraid that soon it will be too big to open!

Thu, 17 May 2007 04:09:00 -0700 from John D
:

I have several worksheets each containing similar information about
different clients and once a fortnight I am expected to send a report on all
activity. To do this I need to collate the information on a daily basis from
each worksheet then present it in the report as a total of all the
worksheets. At present I am doing this by placing formulae on the worksheets
to search out information according to date then add the columns of
information either as a sum or countif equations then gathering and summing
on the report page before actually placing the result in the appropriate
place on the report.
I know there must be a way of doing the same using visual basic but I am
nowhere near clever enough. Does anyone have any ideas? At present I am using
in excess of 52mb and am afraid that soon it will be too big to open!

While it can be done in Excel, as you've proved, I think a database
would be easier to handle and much more versatile for this
application. Since these reports are an ongoing requirement it might
be good to program them in Access using one of its wizards.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Thank you Stan.
I have thought of this but would rather if possible stick to excell. This is
because my boss originally put it together and he has supplied all his sales
staff with it.

"Stan Brown" wrote:

Thu, 17 May 2007 04:09:00 -0700 from John D
:

I have several worksheets each containing similar information about
different clients and once a fortnight I am expected to send a report on all
activity. To do this I need to collate the information on a daily basis from
each worksheet then present it in the report as a total of all the
worksheets. At present I am doing this by placing formulae on the worksheets
to search out information according to date then add the columns of
information either as a sum or countif equations then gathering and summing
on the report page before actually placing the result in the appropriate
place on the report.
I know there must be a way of doing the same using visual basic but I am
nowhere near clever enough. Does anyone have any ideas? At present I am using
in excess of 52mb and am afraid that soon it will be too big to open!

While it can be done in Excel, as you've proved, I think a database
would be easier to handle and much more versatile for this
application. Since these reports are an ongoing requirement it might
be good to program them in Access using one of its wizards.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Fri, 18 May 2007 01:21:00 -0700 from John D
:

"Stan Brown" wrote:
While it can be done in Excel, as you've proved, I think a database
would be easier to handle and much more versatile for this
application. Since these reports are an ongoing requirement it might
be good to program them in Access using one of its wizards.

Thank you Stan.
I have thought of this but would rather if possible stick to excell. This is
because my boss originally put it together and he has supplied all his sales
staff with it.

Yes, I had feared that might be a constraint.

You could still do it in Access, if I am not mistaken, but linking
from the Access database to the Excel workbook. Then everyone still
happily enters data in Excel, but you use Access to generate reports.

I haven't tried it myself. :-)

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Thank you Stan, I think I shall just have to persevere.

"Stan Brown" wrote:

Fri, 18 May 2007 01:21:00 -0700 from John D
:

"Stan Brown" wrote:
While it can be done in Excel, as you've proved, I think a database
would be easier to handle and much more versatile for this
application. Since these reports are an ongoing requirement it might
be good to program them in Access using one of its wizards.

Thank you Stan.
I have thought of this but would rather if possible stick to excell. This is
because my boss originally put it together and he has supplied all his sales
staff with it.

Yes, I had feared that might be a constraint.

You could still do it in Access, if I am not mistaken, but linking
from the Access database to the Excel workbook. Then everyone still
happily enters data in Excel, but you use Access to generate reports.

I haven't tried it myself. :-)

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/