Pass Dynamic Sheet Name + Excel Formula

[Sairam_RPA (Mr.Robot)]

Hi,

I have a complex excel formula like this.

“=IFERROR(SUMIF(‘SheetName’!D:D, “=1”)/(SUMIF(‘SheetName’!D:D, “=1”) - SUMIF(‘SheetName’!D:D, “=-1”)), “”)”

“SheetName” is a dynamic value. How do I use this dynamic variable in excel formula

How do I use this in write cell activity.

When I paste this formula into expression editor. I see error “End of expression expected”

@Yoichi @Palaniyappan

[Edwin_Barahona]

@Sairam_RPA

“=IFERROR(SUMIF(‘SheetName’!D:D, “=1”)/(SUMIF(‘SheetName’!D:D, “=1”) - SUMIF(‘SheetName’!D:D, “=-1”)), “”)”

You’re just missing a couple of double quotes. This might work:

“=IFERROR(SUMIF(‘"+SheetName+"’!D:D, "“=1”")/(SUMIF(‘"+SheetName+"’!D:D, "“=1"”) - SUMIF(‘"+SheetName+"’!D:D, "“=-1"”)), ""“”)"”

[Yoichi]

Hi,

How about to use String.Format as the following?

strFormula =“=IFERROR(SUMIF(‘{0}’!D:D, “”=1"”)/(SUMIF(‘{0}’!D:D, “”=1"“) - SUMIF(‘{0}’!D:D, “”=-1"”)), “”“”)"

Then

String.Format(strFormular,sheetname)

Sequence.xaml (7.5 KB)

Regards,

[Sairam_RPA (Mr.Robot)]

When I paste this is write cell. I see error - " String Constraints must end with double quotes"

[Edwin_Barahona]

There was something with the double quote formatting causing to show compiler erros, I replaced all double quotes and this seems to work fine

"=IFERROR(SUMIF(‘"+ SheetName+"’!D:D, ""=1"")/(SUMIF(‘"+SheetName+"’!D:D, ""=1"") - SUMIF(‘"+SheetName+"’!D:D, ""=-1"")), """")"

On a side note: I believe @Yoichi 's approach is much better, so you can also consider it

[Sairam_RPA (Mr.Robot)]

Thankyou @Yoichi @Edwin_Barahona. Really appreciate your help.

Both your solutions work great.