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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,728
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
Joined
Feb 16, 2013
Messages
6,723
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
Joined
Oct 27, 2005
Messages
19,728
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Hi Ford.....aahh the joys of many minds working together...(y)
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Can you give a sample of the different formulas?
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723

ADVERTISEMENT

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
Joined
Feb 25, 2016
Messages
7
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
Joined
Feb 25, 2016
Messages
7
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

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
Top