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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### 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
14
Replies
0
Views
39
Replies
4
Views
115
Replies
3
Views
30
Replies
0
Views
92

1,171,088
Messages
5,873,749
Members
432,998
Latest member
RachCur

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