H
Harry
Hello All.
I need some advice or paid help from someone in the business of doing the type
of work referred to in the Subject line.
I have a spreadhsheet, very complex with VB scripting. I did not produce it.
It came from a branch of government running a Tribunal that deals with landlord
and tenant disputes in Ontario, Canada. By filling in the various data fields
in the first of the 40 worksheets (the sheet’s labeled DATA), you then hit a
button and it exports the data to one of the other sheets based on which of
their 30 forms you are trying to produce, and it writes to the cells in the
other sheet in a format needed to create an fdf file. Adobe then opens up,
grabs the data in the proper fdf format from the proper worksheet (depending on
the form you’re producing) and imports the data into the editable pdf form.
It’s quite nice, and allows lawyers and paralegals who do a lot of applications
to this quasi-judicial Tribunal to produce professional looking PDF forms from
the excel data file. They can then print them, or fax them, or send them in as
PDFs to the Ontario Rental Housing Tribunal.
Here’s my problem. On the first sheet, the main data sheet with over 800 rows,
the applicant infomation needs to be entered every time I do a form, a notice of
termination, an application for termination, an affidavit of service, a motion
for setting aside a judgement, etc. It’s a pain to enter the applicant
information each time. The applicants are my clients, and I have about 200 of
them at any given timem mostly repeat customers.
I’d like to creatre an Access database, or Filemaker database, or even another
Excel sheet, to keep track of my clients who will eventually be applicants for
the purpose of the spreadsheet. There are 17 fields for "Applicant" in the
spreadsheet. I would rather enter my clients into Access (which I hate) or
Filemaker, or worst case, another Excel sheet (using a spreadsheet to do the job
of a database sucks) and import the PROPER client info when creating a new form
in the complicated spreadsheet rather than re-keying it each time I create a
form.
I’d like to do it as follows. When I come to the first of the 17 "Applicant"
fields on the "Data" sheet of the Tribunal’s worksheet, I’d like to run a macro
(or something) or have it ask me without doing anything, which client I’d like
to import. I’d like it to present me with a pull-down list so I could scroll
through and pick the Client Name from the database, click on it, and all 17
fields in the Access or Filemaker or Excel file would be imported into the
appropriate fields in that worksheet. I’d also be able to decline selecting one
of the existing records (if it’s a new client) and enter the 17 fields manually
into the big Excel Tribunal sheet, and then have it ask me if I would like to
create a new record in my Access or Filemaker database (or Excel spreadsheet).
The agent information fields (that’s me) can be filled in on the big
spreadsheet permanently and saved, since the agent never changes. But the
clients change each time, and it’s a pain to type all that stuff in, when I
typed the same client in the week before. The respondent information (the
person the action is being filed against) is new every time, so I have no choice
but to enter those names every time.
Thanks
Harry
I need some advice or paid help from someone in the business of doing the type
of work referred to in the Subject line.
I have a spreadhsheet, very complex with VB scripting. I did not produce it.
It came from a branch of government running a Tribunal that deals with landlord
and tenant disputes in Ontario, Canada. By filling in the various data fields
in the first of the 40 worksheets (the sheet’s labeled DATA), you then hit a
button and it exports the data to one of the other sheets based on which of
their 30 forms you are trying to produce, and it writes to the cells in the
other sheet in a format needed to create an fdf file. Adobe then opens up,
grabs the data in the proper fdf format from the proper worksheet (depending on
the form you’re producing) and imports the data into the editable pdf form.
It’s quite nice, and allows lawyers and paralegals who do a lot of applications
to this quasi-judicial Tribunal to produce professional looking PDF forms from
the excel data file. They can then print them, or fax them, or send them in as
PDFs to the Ontario Rental Housing Tribunal.
Here’s my problem. On the first sheet, the main data sheet with over 800 rows,
the applicant infomation needs to be entered every time I do a form, a notice of
termination, an application for termination, an affidavit of service, a motion
for setting aside a judgement, etc. It’s a pain to enter the applicant
information each time. The applicants are my clients, and I have about 200 of
them at any given timem mostly repeat customers.
I’d like to creatre an Access database, or Filemaker database, or even another
Excel sheet, to keep track of my clients who will eventually be applicants for
the purpose of the spreadsheet. There are 17 fields for "Applicant" in the
spreadsheet. I would rather enter my clients into Access (which I hate) or
Filemaker, or worst case, another Excel sheet (using a spreadsheet to do the job
of a database sucks) and import the PROPER client info when creating a new form
in the complicated spreadsheet rather than re-keying it each time I create a
form.
I’d like to do it as follows. When I come to the first of the 17 "Applicant"
fields on the "Data" sheet of the Tribunal’s worksheet, I’d like to run a macro
(or something) or have it ask me without doing anything, which client I’d like
to import. I’d like it to present me with a pull-down list so I could scroll
through and pick the Client Name from the database, click on it, and all 17
fields in the Access or Filemaker or Excel file would be imported into the
appropriate fields in that worksheet. I’d also be able to decline selecting one
of the existing records (if it’s a new client) and enter the 17 fields manually
into the big Excel Tribunal sheet, and then have it ask me if I would like to
create a new record in my Access or Filemaker database (or Excel spreadsheet).
The agent information fields (that’s me) can be filled in on the big
spreadsheet permanently and saved, since the agent never changes. But the
clients change each time, and it’s a pain to type all that stuff in, when I
typed the same client in the week before. The respondent information (the
person the action is being filed against) is new every time, so I have no choice
but to enter those names every time.
Thanks
Harry