Hi
I need help writing code in VB to run macro.
I have a EXCEL spreadsheet which contains Year,Quarter, Probability and
amount column.
in year column i have years entry 2001,2002...
in quarter column i have quarter entry Q1,Q2,...
ein Probability column I have prob in percentage 10%,20%,30%...
in Amount i have salary value ... $10000,$50000
Now i am writing macro to sum amount based on user entry

For example in other spread sheet of same excel book user will enter
Enter Year: /or Select from the Drop Down List(2000,2001,...)
Enter Quarter /or Select From the Drop Down List (Q1,Q2,Q3,Q4)
Enter Prob: =90 or between =50 and <=80

After entering this data , it should sum the amount (salary value).
based on this selection i will have four tables for each quarter
For example

Q1 - $xxxxxx (sum of the amount)
Q2 - $yyyyyy (sum of the amount)
Q3 - $jjjjkkkkk (sum of the amount)
Q4 - $eeeeee (sum of the amount)

and if possible it should create chart ( bar chart to show amount for each
quarter)

I am trying to write a code but stuck many places.
can anyone help me to write code.

Thanks!
Kay

You don't need a macro a formula will work

On sheet 1 - Header Row 1, data starts in row 2
Column A - Year
Column B - Quarter (Q1, q2, q3, q4)
Column C - Probability
column D - amount


on Sheet 2

Column A Column B
Row 1 Enter Year 2002
Row 2 Enter Quarter Q2
Row 3 Min Probaility 90
row 4 Max Probability 100

Note: If Probability is less than 50 then enter 0 as min and 50 as max


=sumproduct(--(B1=sheet1!A2:A100),--(B2=sheet1!B2:B100),--(B3<=sheet1!C2:C100),--(B4=sheet1!C2:C100),D2:D100)


"kay" wrote:

Hi
I need help writing code in VB to run macro.
I have a EXCEL spreadsheet which contains Year,Quarter, Probability and
amount column.
in year column i have years entry 2001,2002...
in quarter column i have quarter entry Q1,Q2,...
ein Probability column I have prob in percentage 10%,20%,30%...
in Amount i have salary value ... $10000,$50000
Now i am writing macro to sum amount based on user entry

For example in other spread sheet of same excel book user will enter
Enter Year: /or Select from the Drop Down List(2000,2001,...)
Enter Quarter /or Select From the Drop Down List (Q1,Q2,Q3,Q4)
Enter Prob: =90 or between =50 and <=80

After entering this data , it should sum the amount (salary value).
based on this selection i will have four tables for each quarter
For example

Q1 - $xxxxxx (sum of the amount)
Q2 - $yyyyyy (sum of the amount)
Q3 - $jjjjkkkkk (sum of the amount)
Q4 - $eeeeee (sum of the amount)

and if possible it should create chart ( bar chart to show amount for each
quarter)

I am trying to write a code but stuck many places.
can anyone help me to write code.

Thanks!
Kay

Thanks Joel! this will work, I appreciate your help!
So as formula is already set up it will automatically sum the value for
given data.
Is it possible to write this formula in macro?
as I do not want user to see this formula and also don't want that any one
can modify or play with this formula.
and also Is it possible to create macro to popup chart as soon as user
enters all the data.
User should able to see output and chart.

Thanks!







"Joel" wrote:

You don't need a macro a formula will work

On sheet 1 - Header Row 1, data starts in row 2
Column A - Year
Column B - Quarter (Q1, q2, q3, q4)
Column C - Probability
column D - amount


on Sheet 2

Column A Column B
Row 1 Enter Year 2002
Row 2 Enter Quarter Q2
Row 3 Min Probaility 90
row 4 Max Probability 100

Note: If Probability is less than 50 then enter 0 as min and 50 as max


=sumproduct(--(B1=sheet1!A2:A100),--(B2=sheet1!B2:B100),--(B3<=sheet1!C2:C100),--(B4=sheet1!C2:C100),D2:D100)


"kay" wrote:

Hi
I need help writing code in VB to run macro.
I have a EXCEL spreadsheet which contains Year,Quarter, Probability and
amount column.
in year column i have years entry 2001,2002...
in quarter column i have quarter entry Q1,Q2,...
ein Probability column I have prob in percentage 10%,20%,30%...
in Amount i have salary value ... $10000,$50000
Now i am writing macro to sum amount based on user entry

For example in other spread sheet of same excel book user will enter
Enter Year: /or Select from the Drop Down List(2000,2001,...)
Enter Quarter /or Select From the Drop Down List (Q1,Q2,Q3,Q4)
Enter Prob: =90 or between =50 and <=80

After entering this data , it should sum the amount (salary value).
based on this selection i will have four tables for each quarter
For example

Q1 - $xxxxxx (sum of the amount)
Q2 - $yyyyyy (sum of the amount)
Q3 - $jjjjkkkkk (sum of the amount)
Q4 - $eeeeee (sum of the amount)

and if possible it should create chart ( bar chart to show amount for each
quarter)

I am trying to write a code but stuck many places.
can anyone help me to write code.

Thanks!
Kay