Applying formula to xlsx export file

Stephanus Adisuprijanto shared this problem 19 months ago
Resolved

We have problems with exporting a report to an XLSX file.

The problem is that we cannot apply any formula to the resulting file.

Entering a formula will make the excel to display The Formula instead of the calculation result of the formula.

Best Answer
photo

Hi Stephanus,

ah, I see what you mean!

I have looked into this matter and have found that this is not a bug, it is occurring because Yellowfin uses Excel's "Merge Cells" feature to improve the formatting of the report, and Excel doesn't allow you to directly enter a formula into a merged cell.

This means that if you unmerge all of the merged cells in the spreadsheet then you will be able to do what you want to do.

However, you will also lose the nice formatting of the report, so because of this I played around a bit to see if there was anything else we can do, and it turns out there is:

1. Enter the formula into a non-merged column (for example, in my video you can see that the 1st unmerged column is J).

2. Make sure you make the column reference in your formula absolute (e.g. LEFT($A2, 3))

3. Copy that formula to the column you have inserted into the merged cells.


I hope that helps you, please let me know how you get on with this.

regards,

David

Comments (1)

photo
1

Hi Stephanus,

I have tried this over here but so far have not been able to reproduce the issue.

I have attached a short video to demonstrate my failure.

I was using the 20180104 build of Yellowfin 7.4 and Excel is part of Office 2013.

Could you please watch the video and let me know if the steps I am doing are the correct ones, and also please tell me which build of Yellowfin you are using and which version of Office.

thanks,

David

photo
1

Hi Big Dave,

Please try to insert a new column between column A & column B and then enter a formula like =left(A2,3).

Cause I also have no problem apply a formula in the right side of the report just like the one you showed me in the video.

Regards,

Stephanus

photo
1

Hi Stephanus,

ah, I see what you mean!

I have looked into this matter and have found that this is not a bug, it is occurring because Yellowfin uses Excel's "Merge Cells" feature to improve the formatting of the report, and Excel doesn't allow you to directly enter a formula into a merged cell.

This means that if you unmerge all of the merged cells in the spreadsheet then you will be able to do what you want to do.

However, you will also lose the nice formatting of the report, so because of this I played around a bit to see if there was anything else we can do, and it turns out there is:

1. Enter the formula into a non-merged column (for example, in my video you can see that the 1st unmerged column is J).

2. Make sure you make the column reference in your formula absolute (e.g. LEFT($A2, 3))

3. Copy that formula to the column you have inserted into the merged cells.


I hope that helps you, please let me know how you get on with this.

regards,

David

photo
1

Hi David,

Thanks for the solution. I think it should be good enough for now.

Regards

Stephanus

photo
1

Hi Stephanus,

you're welcome, I'm glad it helps you meet your reporting requirements.

Thanks for letting me know.

regards,

David

photo