List box setup, placement, and functionality

[Ladybug726]

Hi - I created a template/form that will be used by 9 different individuals.
The form will automatically generate a new Service Request number each time
it is opened. Each person will have a set of numbers unique to them. The
template has the following columns:

Col A - Parts, Col B - Qty, Col C - Cost-1, Col D - Tax, Col E - Multiplier,
Col F - Subtotal, and Col G - Cost-2 (or Total). I have formulas in Cols
C-D-F-G to calculate. Column E - Multiplier, is where I need a list box to
appear so the user can tab to that cell and select the percentage from the
list box and have that number inserted automatically into that cell, then the
next cell calculations will be done automatically, as well. (If, of course,
I have done the formulas correctly.)

I figured out how to do a list box and created it on a separate worksheet,
not on my form. But, when I select the percentage I want from the list box,
instead of showing as 10% or 20%, it shows up as 1000% or 2000%. So, I
entered the data for the list as '10%, '20%, etc. and changed the index cell
(the cell to receive the selected percentage) to number general format. By
doing this, it displays properly. Will this cause a problem in my form when
I want it to calculate? If so, how can i remedy this?

My questions are: 1. Is the list box information input and set up on a
separate worksheet then, linked to a particular cell on the form? If so, how
do I do this? 2. How does the list box appear in the Multiplier column on my
form, say in cell G17? 3. How can I get the percentages to show up properly
in cell G17 on my template form other than as I described above?

Can anyone PLEASE help me? I am on a short time-line to get this
accomplished. I must have done, tested, and ready for use within 1 week.

God Bless You, and thank you, in advance for any help you can provide!
Beverly

[Ed Ferrero]

Hi Ladybug726,

Not sure I understand everything you are trying to do, but here are some
comments.

> Col A - Parts, Col B - Qty, Col C - Cost-1, Col D - Tax, Col E -
> Multiplier,
> Col F - Subtotal, and Col G - Cost-2 (or Total). I have formulas in Cols
> C-D-F-G to calculate. Column E - Multiplier, is where I need a list box
> to
> appear so the user can tab to that cell and select the percentage from the
> list box and have that number inserted automatically into that cell, then
> the
> next cell calculations will be done automatically, as well. (If, of
> course,
> I have done the formulas correctly.)

There are actually three types of list boxes that you can embed in a
worksheet. I will take a guess and describe wht=at I think you need.

Lets' say your list is in Sheet2 range A1:A4, and the data you describe
above is in Sheet1.
Enter the following values in Sheet2 range A1:A4
0.1,0.2,0.3,0.4 Format the range as Percentage.

The easiest listbox for your purposes is the cell validation list. To use
this, select some cells in Column E - Multiplier in Sheet1, then use the
menu command Data - Validation...
Change the validation criteria to List. Then click on the source box and
select the list range from your worksheet (or just type =Sheet2!$A$1:$A$4).
Click OK.
Now, if you select one of the cells that you have applied Validation to, you
will see an arrow - click on this to see the list box. You will need to
format the cell as Percentage to see the correct rresult.
Copy the cell down as far as you need.

Ed Ferrero
http://edferrero.m6.net/

[Ed Ferrero]

Hi Beverly,

>Ed:
>
>I got the percentages to show up in the list box as I wanted.
> (I'm not exactly certain HOW I did it). I copied the List
> Box down the Multiplier column
> Problem: When I select the percentage I want for a parts item,
> that percentage number is removed from the list box! When I
> get down to the 4th or 5th entry, only a few numbers remain in the box.
>
> The percentages I'm using are: 10%, 12%, 15%, 20%, 25%, 30%, 35%,
> 40%, and 45%
>
> Whichever one I select for an entry is removed from the list box.
> Any suggestions on this?

I have not seen this behaviour before, so I am not sure how to help you.

There is an illustrated tutorial on how to do this stuff at
http://edferrero.m6.net/DataTutor01.shtml Hope that helps.

BTW, I prefer to answer queries through the newsgroup.

Ed Ferrero
http://edferrero.m6.net

[Ladybug726]

Hi Ed:

Sorry to have bothered you with emails. I did get the percentages to work
properly, as well as the list box and calculations. It was trial & error,
but I finally figured it out and everything works beautifully!!! Thank you
so much for your assistance. Have a bountiful Thanksgiving.

"Ed Ferrero" wrote:

> Hi Beverly,
>
> >Ed:
> >
> >I got the percentages to show up in the list box as I wanted.
> > (I'm not exactly certain HOW I did it). I copied the List
> > Box down the Multiplier column
> > Problem: When I select the percentage I want for a parts item,
> > that percentage number is removed from the list box! When I
> > get down to the 4th or 5th entry, only a few numbers remain in the box.
> >
> > The percentages I'm using are: 10%, 12%, 15%, 20%, 25%, 30%, 35%,
> > 40%, and 45%
> >
> > Whichever one I select for an entry is removed from the list box.
> > Any suggestions on this?
>
> I have not seen this behaviour before, so I am not sure how to help you.
>
> There is an illustrated tutorial on how to do this stuff at
> http://edferrero.m6.net/DataTutor01.shtml Hope that helps.
>
> BTW, I prefer to answer queries through the newsgroup.
>
> Ed Ferrero
> http://edferrero.m6.net
>
>
>