Excel Formula embedded in comment

[Rookie_of_the_year]

Hello Everyone,

Could someone let me know if there is a way to add the results of a calculation into the comment for a selected range? if so, could the results in the comment change if any of the values change?

GM% FormulaCustomer A Price - Cost) / (Customer A Price)
​

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]Cost
[/TD]
[TD]Customer A Price
[/TD]
[TD]Customer B Price
[/TD]
[TD]Customer C Price[/TD]
[TD]GM% Customer A
[/TD]
[TD]GM% Customer B
[/TD]
[TD]GM% Customer C
[/TD]
[/TR]
[TR]
[TD]101.24
[/TD]
[TD]216.35
[/TD]
[TD]195.00
[/TD]
[TD]200.00
[/TD]
[TD]

<colgroup><col width="115"><col width="114" span="2"></colgroup><tbody>
[TD="class: xl65, width: 115, align: right"] 53.21%
[/TD]
[TD="class: xl65, width: 114, align: right"][/TD]
[TD="class: xl65, width: 114, align: right"][/TD]

</tbody>

[/TD]
[TD] 48.08%
[/TD]
[TD]49.38%
[/TD]
[/TR]
[TR]
[TD]112.10
[/TD]
[TD]190.25
[/TD]
[TD]171.00
[/TD]
[TD]176.00
[/TD]
[TD]

<colgroup><col width="115"></colgroup><tbody>
[TD="class: xl63, width: 115, align: right"]41.08%
[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="114"></colgroup><tbody>
[TD="class: xl65, width: 114, align: right"]34.44%
[/TD]

</tbody>

[/TD]
[TD]36.31%
[/TD]
[/TR]
[TR]
[TD]112.41
[/TD]
[TD]197.45
[/TD]
[TD]177.00
[/TD]
[TD]182.00
[/TD]
[TD]

<colgroup><col width="115"></colgroup><tbody>
[TD="class: xl65, width: 115, align: right"]43.07%
[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="114"></colgroup><tbody>
[TD="class: xl65, width: 114, align: right"]36.49%[/TD]

</tbody>

[/TD]
[TD]38.24%
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

<colgroup><col width="78"></colgroup><tbody>
[TD="class: xl65, width: 78"][/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]













The desired outcome would be to have a comment for each customer price (i.e. GM% 43.07%).

Please let me know if additional information is required.

Thanks again for taking the time to review my post!

 

[shg]

It would require VBA.

Why not just put the formulas in cells in the usual way?

 

[Rookie_of_the_year]

It would require VBA.

Why not just put the formulas in cells in the usual way?

I am looking for the VBA to handle my request. In a workbook with multiple price columns can become cumbersome when having to add a gross margin percentage for each column with a price.

 

[shg]

If you convert the range into a table, the table will extend and the formulas will enter automatically as you add more data.

 

[Rookie_of_the_year]

If you convert the range into a table, the table will extend and the formulas will enter automatically as you add more data.

That is correct. However, I’m trying to find a better solution to having spreadsheets that go out to column AFD. Would you happen to know the VBA code needed to complete my request?