#### 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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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 ???

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

Hi Ford.....aahh the joys of many minds working together...

Can you give a sample of the different formulas?

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

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)))

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:
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
7
Views
219
Replies
5
Views
428
Replies
5
Views
406
Replies
2
Views
158
Replies
2
Views
140

1,211,805
Messages
6,104,083
Members
447,893
Latest member
Hoosier605

### 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.

### Which adblocker are you using?

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