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)
​

Cost	Customer A Price	Customer B Price	Customer C Price	GM% Customer A	GM% Customer B	GM% Customer C
101.24	216.35	195.00	200.00	53.21% <colgroup><col width="115"><col width="114" span="2"></colgroup><tbody> </tbody>	48.08%	49.38%
112.10	190.25	171.00	176.00	41.08% <colgroup><col width="115"></colgroup><tbody> </tbody>	34.44% <colgroup><col width="114"></colgroup><tbody> </tbody>	36.31%
112.41	197.45	177.00	182.00	43.07% <colgroup><col width="115"></colgroup><tbody> </tbody>	36.49% <colgroup><col width="114"></colgroup><tbody> </tbody>	38.24%
						<colgroup><col width="78"></colgroup><tbody> </tbody>

<tbody>
</tbody>

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?