Dynamic view entry formatting causing issues on source sheet

Tanya Boehmke

Hello,

I have a Dynamic View set up to capture dollar amounts, the source sheet is formatted to currency for these cells. The issue is the data is mostly entered using copy/paste which is preferred to prevent typing errors, and the dollar sign is copy/pasted along with the number. When pasting this way it fills in the sheet in different formatting which creates issues with the formulas, shown below.

I realize I could remove the currency setting on these cells in the source sheet, however in the case a dollar sign is not copy/pasted that will create new issues. Is there any other way to format the dynamic view data cells to not auto-populate a dollar sign or error out the formulas?

Tanya Boehmke

@Paul Newcome you're my resident hero, any ideas on this one?

Paul Newcome

When it is copy/pasted with the dollar sign, it is being stored as text instead of a number. To accommodate the copy/paste, you can use a helper column to convert the pasted string into a number value which can then be used in further calculations, or you can drop the whole thing below in place of direct cell references in existing formulas.

=VALUE(SUBSTITUTE(SUBSTITUTE([Column Name]@row, "$", ""), ",", ""))

Tanya Boehmke

@Paul Newcome thank you

Paul Newcome

Happy to help. 👍️