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

[Anand_Designer (Anand_RPA)]

Hi,

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)

[Lak_Ui (Lakshmi)]

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

[Sreelatha278 (Sree Latha Reddy)]

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

[Anand_Designer (Anand_RPA)]

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

[Lak_Ui (Lakshmi)]

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

[Anand_Designer (Anand_RPA)]

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

[Lak_Ui (Lakshmi)]

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

[Anand_Designer (Anand_RPA)]

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

[Anand_Designer (Anand_RPA)]

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

[Lak_Ui (Lakshmi)]

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

[Lak_Ui (Lakshmi)]

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

[Anand_Designer (Anand_RPA)]

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)

[Lak_Ui (Lakshmi)]

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

[Anand_Designer (Anand_RPA)]

its working fine.Thank you

[Aleem_Khan (crazy bot)]

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