Creating a Flexible Proposal Form

[Jason Roberts]

I'm redesigning forms for our small business, trying to simplify processes
using the tools available in Office. So far I've been very successful in
making things easier for the owner of the business, who is not very
computer-literate, but this latest one has me stumped.

She has an itemized proposal form which varies from client to client. Right
now, that form is in Word, but I am posting here because I believe Excel to
be the better tool for this purpose. The basic format would be a number
sections, each with line items with dollar amounts, and a grand total. In
other words, something like this:

SECTION I
item a................................$ 1,200.00
item b................................$ 800.00
item c................................$ 1,700.00

SECTION II
item d................................$ 1,000.00

SECTION III
item e................................$ 700.00
item f.................................$ 1,300.00
item g................................$ 500.00

TOTAL..............................$ 6,200.00

That's the basic idea, although there's obviously more to it than that.
Right now, as I said, the form is in Word, and it looks great, but she has to
add up the numbers by hand. Yes, I know you can put a spreadsheet into Word,
but it seems to me that since the document is almost totally spreadsheet it
may make sense to put it in Excel. I am familiar with Excel and could whip up
such a simple format with no problems, if I were the one using it; the
challenge is making it something easy enough for HER to use.

If the format were constant, if each section had a set number of lines, it
would be easy. That's not the case, though. A section could have one line or
25 lines, so I can't make a form, leave blank lines, and have her fill it in.
I need a way for a non-Excel literate user to easily be able to add and/or
delete lines in each section.

As I said, this needs to be as simple as simple can be. If there is any room
for error, she will find it. I'd considered creating a data entry marco of
sorts, which would create a heading and then allow her to enter lines before
she clicked a button and allowed it to create the next heading, and so on,
but she also needs the flexibility to go back and modify these proposals. Can
I create a form with buttons on it? (I know it can be done in Word, although
I've never done it.) Is there a formula that allows import of varying numbers
of rows from another sheet, perhaps? I'm willing to listen to any suggestions
and read any tutorials you point me to, but I have been playing and
researching this all week without finding a solution.

If you feel this is a task better suited to Word for some reason, please say
so and give your suggestions there, but, in my experience, spreadsheets
within Word documents can be easily corrupted by an uneducated user.

I look forward to your suggestions.

Regards,

-J.

[Anne Troy]

I do feel it'd be better done in Word, Jason, because you're not adding up
much stuff, right? Think about this...
Create an autotext entry for each "section type." (Make the section # be a
numbered style.) Now, record macros that insert each of the autotext
entries, and give them names (on a toolbar from which she can run the
macros) that she'll recognize. If you place the autotext entries in tables,
then you ought to be able to have a final table cell using =Sum(above) as
the calculation in a calculated field. This probably sounds harder than it
really is.
************
Anne Troy
www.OfficeArticles.com

"Jason Roberts" <[email protected]> wrote in message
news:[email protected]...
> I'm redesigning forms for our small business, trying to simplify processes
> using the tools available in Office. So far I've been very successful in
> making things easier for the owner of the business, who is not very
> computer-literate, but this latest one has me stumped.
>
> She has an itemized proposal form which varies from client to client.
> Right
> now, that form is in Word, but I am posting here because I believe Excel
> to
> be the better tool for this purpose. The basic format would be a number
> sections, each with line items with dollar amounts, and a grand total. In
> other words, something like this:
>
> SECTION I
> item a................................$ 1,200.00
> item b................................$ 800.00
> item c................................$ 1,700.00
>
> SECTION II
> item d................................$ 1,000.00
>
> SECTION III
> item e................................$ 700.00
> item f.................................$ 1,300.00
> item g................................$ 500.00
>
> TOTAL..............................$ 6,200.00
>
> That's the basic idea, although there's obviously more to it than that.
> Right now, as I said, the form is in Word, and it looks great, but she has
> to
> add up the numbers by hand. Yes, I know you can put a spreadsheet into
> Word,
> but it seems to me that since the document is almost totally spreadsheet
> it
> may make sense to put it in Excel. I am familiar with Excel and could whip
> up
> such a simple format with no problems, if I were the one using it; the
> challenge is making it something easy enough for HER to use.
>
> If the format were constant, if each section had a set number of lines, it
> would be easy. That's not the case, though. A section could have one line
> or
> 25 lines, so I can't make a form, leave blank lines, and have her fill it
> in.
> I need a way for a non-Excel literate user to easily be able to add and/or
> delete lines in each section.
>
> As I said, this needs to be as simple as simple can be. If there is any
> room
> for error, she will find it. I'd considered creating a data entry marco of
> sorts, which would create a heading and then allow her to enter lines
> before
> she clicked a button and allowed it to create the next heading, and so on,
> but she also needs the flexibility to go back and modify these proposals.
> Can
> I create a form with buttons on it? (I know it can be done in Word,
> although
> I've never done it.) Is there a formula that allows import of varying
> numbers
> of rows from another sheet, perhaps? I'm willing to listen to any
> suggestions
> and read any tutorials you point me to, but I have been playing and
> researching this all week without finding a solution.
>
> If you feel this is a task better suited to Word for some reason, please
> say
> so and give your suggestions there, but, in my experience, spreadsheets
> within Word documents can be easily corrupted by an uneducated user.
>
> I look forward to your suggestions.
>
> Regards,
>
> -J.
>
>