Months Count

[KrisCz]

Hi Guys,
I went through forum but couldn't find similar issue, I am not great excel user but tried to search solution via google as well, but don't know how to phrase it correctly.
I hope someone will be able to help, I created formula which counts months from current date =(YEAR(W2)-YEAR(H2))*12+MONTH(W2)-MONTH(H2)
My current date is in "W2" and I would like to formula to have "W2" when I drag formula down(which is in "I2" but it changes to "W3", "W4" and so on as normally it is needed like that.
Is there a way to use data only from W2 without manually overwriting every fomula or dragging current date down?

Thanks in advance

Tracker Insurance.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2					01/02/2020	21														01/11/2021
3					01/05/2021	-1456
4					10/06/2019	-1433
5						0
6						0
7						0
8						0
9						0
10						0
11						0
12						0
13						0
14						0
15						0
16						0
17						0
18						0
19						0
20
Sheet1

Cell Formulas
Range	Formula
W2	W2	=TODAY()
I18,I16,I14,I2:I12	I2	=(YEAR(W2)-YEAR(H2))*12+MONTH(W2)-MONTH(H2)
I13,I19,I17,I15	I13	=(YEAR(W12)-YEAR(H13))*12+MONTH(W12)-MONTH(H13)

Cells with Data Validation
Cell	Allow	Criteria
G2:G20	List	Issued, Underwriting, Postpned, Lapsed, Declined, Cancelled, Awaiting Start Date,
K2:K20	List	Yes, No
U2:U20	List	Yes, No
P2:P1048576	List	Indemnity, Non-Indemnity
Q2:Q1048576	List	Yes, No
E2:E1048576	List	Life, Critical Illness, Life & CIC, Income Protecion

 

[Fluff]

Hi & welcome to MrExcel.
You need to anchor the row number for W2 like

=(YEAR(W$2)-YEAR(H2))*12+MONTH(W$2)-MONTH(H2)

 

[KrisCz]

Hi & welcome to MrExcel.
You need to anchor the row number for W2 like

=(YEAR(W$2)-YEAR(H2))*12+MONTH(W$2)-MONTH(H2)

Thanks, that was super quick, will that anchor works with other types of furmulas?
I had the same problem in the past when I had value in a2 and had to drag it down every time when I changed value in a2 cell.

 

[Fluff]

Yes it works for all formulae, have a look here to understand about absolute & relative cell references. Relative and absolute cell reference: why use $ in Excel formula - Ablebits.com

 

[KrisCz]

Yes it works for all formulae, have a look here to understand about absolute & relative cell references. Relative and absolute cell reference: why use $ in Excel formula - Ablebits.com

Thank You, I'll have a look there.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[KrisCz]

One more thing, How to keep cells empty in "I" column until i put value in "H"?

 

[Fluff]

How about

=IF(H2="","",(YEAR(W$2)-YEAR(H2))*12+MONTH(W$2)-MONTH(H2))

 

[KrisCz]

How about

=IF(H2="","",(YEAR(W$2)-YEAR(H2))*12+MONTH(W$2)-MONTH(H2))

It works, had to put h3 as added one more row. Thanks again.

 

[Fluff]

My pleasure.