Determining where a trailing stop gets triggered

[oldschooltrader]

Hello everybody,
I have a spreadsheet in the following form (please let me know if the image didn't upload properly):
Excel(Trailing).png
The actual spreadsheet is more complicated with a lot of columns but this gives the main information that I get out of it.

The first column has the closing prices of every 1HR bar.
The second column will have a value only if a trade has been entered (+ve if it is a buy, -ve if it is a sell).
The third column has the initial stop-loss simply calculated as the entry level +or- 1pt.
The fourth column has the take-profit value simply calculated as the entry level +or- 2pts.
As you can see a lot of trades in either direction can currently be opened at any one time.

I am trying to incorporate a trailing loss system whereby the stop is moved so that it is always at max 1pt worse than the current closing price.
So in the first example, where we bought at 81.981 with initial stop of 80.981. If the close in a few bars rises to 82.45 then I want to move the stop to 81.45.
Eventually we get to a point where the trailing stop would stop updating because of 1 of 2 things occurring.
1) the take profit level gets hit. So in the above example the take profit was at 83.981. The last trailing stop would be around 1pt away from here then (depending on where the close was).
2) the price moves against and the trailing stop gets triggered.

I currently know how to calculate the number of rows away that the take-profit gets triggered since it is a static number I am searching for.
However I am having difficulty doing the same for the trailing stop since this number is supposed to be updated with the new closing prices (depending on if it is moving in our favor).
In the final column where I left "?" I am trying to determine where the trailing stop will end up getting triggered. Ideally, trying to figure out how many rows away from the trade entry it is.
Is there a way to use excel formulas in the cell to try and achieve this (by index() or match() or something like that) or will a macro have to be created for this purpose?

Thank you for your help.

[vlady]

Moderator's Note:


Welcome to the forum since your new to the forum may i suggest you to attach a real workbook. Typing data from a picture or paste text from your post into a spreadsheet as a hindrance to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

Nice to have you with us.
regards,
Vladimir

[oldschooltrader]

Thank you for the advice Vladimir. I have attached a workbook as well.