stats from data GQ

[Moh]

I have created a excel sheet which has data in columns, which are column D
called Products and column F called Quantity. this data is made up from a
list using VLOOKUP for bothe columns.

each cell in column D (Products) from d5 to d39 has the following data
(D539) :

Select from list
Acquisition
Broadband Lite
Broadband Network
Broadband Network 2 Year
Broadband Single
Broadband Single 2 Year
Broadband_Share
Broadband_Share 2 Year
Bundle - Double
Bundle - Multi
Bundle - SUPER
Business Line Divert
CPE Sales
CNI
Direct Debit
Featurebound
Featureline 1 Year
Featureline 3 Year
Featureline 5 Year
Featureline Compact 1 Year
Featureline Compact 3 Year
Featureline Compact 5 Year
Fusion
Inbound Services
Maintanance
Mobile Low Tier
Mobile Medium Tier
Mobile High Tier
Openzone
PSTN
RCF
Switch
Telephone Handset
VAS - BusEmail
VAS - BusEmail Share
VAS - Datasure
VAS - IBP
VAS - ITP
VAS - ITSM
VAS - ITSupport Server
VAS - Security Pack
VAS - Static IP
VOIP

each cell in column F (Quantity) from F5 to F39 has the following data
(F5:F39) :

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Basically when the user selects a product from the product list it, they
will then choose how many they are selling. the same product can be sold
again and again. so really i need a code that can do the following:
1) a commandbutton that you click
2) it will bring up a userform or textbox or even better a list with all the
products above.
3) it allows you to either type in a product or pick from the list
4) once selected it should return with the total of that product sold.

if someone can help me with this i will be very amazed.

thank you

 

[JLatham]

Are the values in column F supposed to get updated based on the entry the
user makes on the form? You now show them simply as incrementing from row to
row, so we're not sure of how to deal with the quantity entered on the user
form.

 

[Moh]

the values in column F is selected from a list by the user. same with column
D. so basically the user selects a product from any row in column D and the
the user selects how many they are selling in column F. IF YOU IT MIGHT HELP
DO YOU WANT ME TO E-MAIL YOU THE FORM?

 

[JLatham]

That might help - send as attachment to (e-mail address removed) and I'll
look and see what we can do.

I guess my basic question is what do we do with the information, especially
the quantity, once they've entered it. I presume it's to be added to another
value (previous sales count) somewhere else? Hopefully I'll be able to see
that in the workbook or on the form you provide.

 

[Moh]

i've sent it to you. if anyone else can figure it out pls do help

 

[JLatham]

After seeing the workbook, it appeared that the problem was that a quantity
was being selected from a list of numbers and that there was no way to track
that value through to future changes in the workbook.

Additionally there was an associated "product" (column D) that they could
choose from to indicate what it was that they sold. But since they might
sell the same thing to different clients, they needed a way to collate the
sales of each product on a second sheet where the source for both lists is
kept.

My solution: Use the main sheet's _Change() event to detect a change in
either the Product sold or the Quantity sold and then transfer the quantity
to a "current sales" column on the list sheet. I also added a "previous
sales" column and a Total Sales column on it.

When the workbook is opened, the values in Previous Sales are set to the
Total Sales and all Current Sales quantities are reset to zero.

Basic problem: no way to track quantities from previous copies of the
workbook.

 

[Moh]

Thank you so much JLatham you solved all the problems.