evol25

New Member
Hi I'm new to this forum I signed up to ask this question...searched google couldnt find it

I want to change formulas based on a drop down list made in data validation. The spreadsheet calculates qtq% and yty%. Instead of having to manually change this every month and every quarter, I want to create a drop down list that has 1q, jan, feb, mar, 2q, etc and depending on which I click on the formulas will automatically change.

I do not know a simple way to do this or how to do it using vba. If someone could help me out I'd really appreciate it. thanks

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Michael M

Well-known Member
I don't think the DV is the way to go !
I think you'll need to resort to VBA.
But an easier option would be to have the 4 quarters side by side, with the appropriate formulae in each one ???

FDibbins

Well-known Member
Sorry Michael, but I think DV is just what is needed here. I will see what I can put together

Michael M

Well-known Member
Hi Ford.....aahh the joys of many minds working together...

Scott Huish

MrExcel MVP
Can you give a sample of the different formulas?

FDibbins

Well-known Member
This is what I came up with...
 A​ B​ C​ D​ E​ 1​ Date Qtr Amt Jan 2​ 1/1/2015​ 1​ 10​ 10​ 3​ 2/2/2015​ 1​ 20​ 4​ 3/6/2015​ 1​ 30​ 5​ 4/7/2015​ 2​ 40​ 6​ 5/9/2015​ 2​ 50​ 7​ 6/10/2015​ 2​ 60​ 8​ 7/12/2015​ 3​ 70​ 9​ 8/13/2015​ 3​ 80​ 10​ 9/14/2015​ 3​ 90​ 11​ 10/16/2015​ 4​ 100​ 12​ 11/17/2015​ 4​ 110​ 13​ 12/19/2015​ 4​ 120​
E2=IF(LEFT(E1,1)="Q",SUMIFS(\$C:\$C,\$B:\$B,RIGHT(E1,1)),SUMIFS(\$C:\$C,\$A:\$A,">="&DATEVALUE(1&E1&2015),\$A:\$A,"<"&EDATE(DATEVALUE(1&E1&2015),1)))

G2:G17 = DV list
E1=drop-dpwn

FDibbins

Well-known Member
If you just use 1, 2, 3, 4 for the QTR, then that can be shortened a bit to this...
=IF(ISNUMBER(E1),SUMIFS(\$C:\$C,\$B:\$B,RIGHT(E1,1)),SUMIFS(\$C:\$C,\$A:\$A,">="&DATEVALUE(1&E1&2015),\$A:\$A,"<"&EDATE(DATEVALUE(1&E1&2015),1)))

evol25

New Member
Can you give a sample of the different formulas?

Here is my spreadsheet layout (all numbers and labels have been changed/sensitive information collapsed). This example shows quarter to quarter and year to year percent increase/decrease and change in dollar amounts based on 4Q. So QtQ is 4Q to 3Q and YtY is 4Q 2015 to 4Q 2014.

I want a drop down that has 1Q, Jan, Feb, Mar, 2Q, Apr, etc.. and when I change it to 3Q for example the new equation in BF8 should be AD8/Z8-1. I didn't quite follow the sumif equations that were given above. Thanks for the input it might be valid and I just didn't understand it.

Last edited by a moderator:

evol25

New Member
Here is my spreadsheet layout (all numbers and labels have been changed/sensitive information collapsed). This example shows quarter to quarter and year to year percent increase/decrease and change in dollar amounts based on 4Q. So QtQ is 4Q to 3Q and YtY is 4Q 2015 to 4Q 2014.

I want a drop down that has 1Q, Jan, Feb, Mar, 2Q, Apr, etc.. and when I change it to 3Q for example the new equation in BF8 should be AD8/Z8-1. I didn't quite follow the sumif equations that were given above. Thanks for the input it might be valid and I just didn't understand it.

Sorry I copy n pasted a picture and clearly the ****t didnt work..a mod or something can delete my post i don't see that option. I recreated the picture I was going to post. The layout is Quarter, Months, Quarter, Months.....Full Year

This example shows quarter to quarter and year to year percent increase/decrease and change in dollar amounts based on 4Q. So QtQ is 4Q to 3Q and YtY is 4Q 2015 to 4Q 2014.

I want a drop down that has 1Q, Jan, Feb, Mar, 2Q, Apr, etc.. and when I change it to 3Q for example I'd want the equation in M3 to become =E3/B3-1. I didn't quite follow the sumif equations that were given above. Thanks for the input it might be valid and I just didn't understand it.

Now If I changed the drop down to
 A B C D E F G H I J K L M N 1 2014 2015 4Q 2 3Q 4Q 3Q JUL AUG SEP 4Q QTQ% QTQ\$ YTY% YTY\$ 3 REV 100 300 250 100 100 50 400 =I3/E3-1 =I3-E3 =I3/C3-1 =I3-C3 4 COST 50 100 125 50 50 25 150 =-(I3/E3-1) =E3-I3 =-(I3/C3-1) =C3-I3 5 GPM 50% 67% 50% 50% 50% 50% 63% =(I5-E5)*100 =(I5-C5)*100 6 7

<tbody>
</tbody>

Replies
1
Views
24
Replies
0
Views
47
Replies
4
Views
115
Replies
3
Views
62
Replies
0
Views
114

1,171,794
Messages
5,877,555
Members
433,265
Latest member
Umaratnam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back