How To Change Formulas Based on a Prompt? Please Help

evol25

New Member
Joined
Feb 25, 2016
Messages
7
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 ???
 
Upvote 0
Sorry Michael, but I think DV is just what is needed here. I will see what I can put together
 
Upvote 0
Hi Ford.....aahh the joys of many minds working together...(y)
 
Upvote 0
This is what I came up with...
A​
B​
C​
D​
E​
1​
DateQtrAmtJan
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
 
Upvote 0
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)))
 
Upvote 0
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:
Upvote 0
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
25150
=-(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>
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top