I build homes. I use Excel for my scheduling needs.

Currently I have my excel schedule set up as follows (Company mandated - can't change this) with a link to a task that pulls up an email addressed to a particular person with a pre-filled subject and body text. Such as in the following:

Capture1.JPG

Now, I need to be able to "insert" the date from ROW 2 into my email pop-up (circled in red in the image above) of what ever column the task is in without messing up the formula or getting any errors.

The key is that these tasks move around all of the time. But, the dates in Row two never move, however they change from house to house as we start them on different dates, etc.

So ultimately:

1. I move the task to the selected date that I want it on;
2. Click on the task hyperlink;
3. My email program pops up, with the TO line filled out, SUBJECT line filled out, BODY filled out (already working) ***AND*** the DATE from ROW 2 inserted where I need it... as follows:

Capture2.JPG

My current hyperlink formula is: =HYPERLINK("mailto:" & 'Beta - Sandbox'!B1 & "?subject="& Schedule!B3 & "&body=" & 'Beta - Sandbox'!C1, "Lot survey")

I need this changed to include the insert feature for the date in ROW 2 of whatever column I move the task to. I have included a copy of my excel spreadsheet for your review.


If you know how to do this, please let me know. Looking forward to a fruitful discussion - Thank you in advance!!!

0_DO NOT OVER-WRITE TEMPLATE FORUM.xlsx