Inserting cell value from stationary row when link changes columns... HELP!

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!!!

Try this is Beta - Sanbox C1:

Formula:

Please Login or Register  to view this content.

I just realized you wanted it to be the date in F3, is this always F3 or is it always the date above the Lot Survey? How do you determine what date?

Whatever column I move the lot survey task to, I need the email to populate the date in the preset body text in ROW 2 of whatever column I move the task to. Does that make any sense?

Okay we are getting somewhere now...

My insert formula is now: ="Please schedule the Lot/Pad survey on: "&TEXT(Schedule!F2,"mm/dd/yy")

PROBLEM: How do I make the "F" above change as I drag around the task on my sheet? For example, if I move my task to column, E or H, I will need the "F to change automatically. Or just replace "F with something that means "same column as we are already in"....

Thoughts???