How To Change Formulas Based on a Prompt? Please Help

[evol25]

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

 

[Michael M]

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]

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

 

[Michael M]

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

 

[Scott Huish]

Can you give a sample of the different formulas?

 

[FDibbins]

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]

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]

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.

 

[evol25]

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>