@LJohnson1690 Not sure what you mean by "formula with a comment dependent". Then, you mention that you enter a reference to B6 for those cells that need to converted to CAD.
Perhaps I'm totally missing the point, but the attached file contains an example of how I would tackle a situation where amounts are entered in multiple currencies and need to be summarised/reported in one currency. See if anything makes sense to you. If not, kindly ignore this post and clarify your question. Perhaps a good idea to upload a copy of your own workbook.
