Calculated Formula error In Table

%3CLINGO-SUB%20id%3D%22lingo-sub-2753231%22%20slang%3D%22en-US%22%3ECalculated%20Formula%20error%20In%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2753231%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3EI%20have%20default%20data%20set%20in%20a%20cell%20as%20%22Submitted%22%20to%20indicate%20the%20status%20of%20a%20quote.%3C%2FP%3E%3CP%3EThis%20was%20done%20using%20the%20formula%26nbsp%3B%3DIF(S4%3D%22%22%2C%22Submitted%22)%20in%20a%20power%20query%20table.%20Then%20using%20data%20validation%20i%20can%20select%20won%20or%20lost%20as%20other%20statuses%20for%20the%20quote%20in%20the%20same%20column.%20The%20problem%20is%20when%20i%20refresh%20the%20table%20to%20pull%20in%20new%20date%2C%20the%20cells%20revert%20back%20to%20%22submitted%22.%20How%20do%20I%20make%20the%20formula%20null%20once%20i%20manually%20change%20the%20cell%20data%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2753231%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Hello!

I have default data set in a cell as "Submitted" to indicate the status of a quote.

This was done using the formula =IF(S4="","Submitted") in a power query table. Then using data validation i can select won or lost as other statuses for the quote in the same column. The problem is when i refresh the table to pull in new date, the cells revert back to "submitted". How do I make the formula null once i manually change the cell data?

 

Thanks in advance

1 Reply

@David_Contour 

 

by using VBA to propagate the submitted status to the source dataset