Update Forumula Dynamically On NET column based on Filter Transaction type = ACQUIRING


i have excel it has 6 columns. in NET column i have to update formula like–>=C2-D2-E2 . condition is If Transaction Type is =ACQUIRING
then update formula like -->C2-D2-E2
we have update formula dynamically.

can any one help ?

Update Formula_Acquiring.xlsx (9.3 KB)

you can use write cell activity and specify value as =C2-D2-E2
then use Auto fill range activity with source range as F2 and fill range as F2:F+total no of rows

Hello @Anand_Designer ,

If you want to automate this, follow below steps

Read excel to data
For each Row in Data table
If Row(“Transaction Type”).Equals(“ACQUIRING”)
then assign Row(“NET”) = Convert.ToDouble(Row(“Amount”))-Convert.ToDouble(Row(“Commission”))-Convert.ToDouble(Row(“GST”))
Write datable to excel

1 Like

its working fine. but how will we show formula when i click on particular cell?
Like F4–>C4-D4-E4

Excel.xaml (6.4 KB)
Can you check this?

i checked this , it showing formulas. but it filling all rows. here condition is only when Transaction Type =ACQUIRING to aplly formula remain Transaction Type =Refund no need to apply this formula

is it ok to rearrange the rows like all acquiring together?

we have to fill only Acquiring rows… remain rows have another formula is there .

for refund like this formula==C3-D3-E3+F3
For acquiring ===C2-D2-E2
Acquire and Refund not come in same order it will be vary.

Excel.xaml (9.7 KB)
Check if this works for you…use the original input excel and output excel is test.xlsx

if not please let us know we can check for some other logic

hi i am little bit confusing to apply both formula for your xaml.

i am adding here latest excel file . can you look it ?
Update Formula_Acquiring.xlsx (9.8 KB)

Excel.xaml (12.9 KB)
Please have a look into the updated code and if it works for you

1 Like

its working fine.Thank you

1 Like

Hi I am not able to open this file can you please let me know how to resolve this problem.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.