Change formula reference by clicking on a cell.

[purlo]

I have a simple formula:
=DATE(YEAR(A1)-B1,MONTH(A1),DAY(A1))
In formula I would like to change B1 reference only by clicking on certain B:B value.
The reason: I want to have only one cell with above formula. But I need it to be dynamical in order to check new value upon request.
Is it possible to do something like this?

[gue2013]

This will only work if the data in B:B is an integer! In your sample file it is always x.5.
If you know that is alwys some years and half a year then you have to change your formula to

Please Login or Register  to view this content.

or better if other fractions should be allowed (and remainingdays ignored)

Please Login or Register  to view this content.

Then you have to convert .xlsx to .xlsm and add the event "Worksheet_SelectionChange" as I did in the attachment

cheers