Text String expression to auto calculate in Excel

I have a report that needs to be able to be changed once its sent out to the field in excel. I have found a work around that allow me to set up the formula the way I need it to be but when I pull it up in excel all I see is the text for the formula. I then have to go to that cell, put my cursor in the formula box and hit enter for it to automaticaly calculate. How can I get it so that I don't have to do that last step?
Here is the text string I put in the expression
="=""$""&SUM(COUNTIF(INDIRECT(ADDRESS(ROW(), 6)&"":""&ADDRESS(ROW(), COLUMN()-2)),"">=20"")-(COUNTIF(INDIRECT(ADDRESS(ROW(), 6)&"":""&ADDRESS(ROW(), COLUMN()-2)),"">=40"")))*15"

Open in new window

expexc.xls
salesanalystspiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ardhendu SarangiSr. Project ManagerCommented:
HI there,
the reason you were seeing the formula was because of a formatting issue. I have changed the formatting to General instead of Text and you should be fine now. Pls see attached.

- Ardhendu
expexc.xls
0
salesanalystspiAuthor Commented:
How do I change that format from with in SSRS?
0
Ardhendu SarangiSr. Project ManagerCommented:
Whats SSRS? sorry i didn't get it...
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

salesanalystspiAuthor Commented:
The report is being generated out of MS SQL Server Reporting Services. I do not care about being able to see the formulas from the web portal in there but I do need to be able to see results of the formula in excel. Does that make sense?
0
Ardhendu SarangiSr. Project ManagerCommented:
I am not familiar with the SSRS but I can give you a macro which you can run on the excel after it is created.
This should resolve the issue.

- Ardhendu

p.s: BTW, while creating this report in SSRS, do you have any options which changes the formatting of the columns as TEXT instead of GENERAL?  Can you check?


Sub Macro1()
'
    Columns("L:L").Select
    Selection.TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1)), TrailingMinusNumbers:=True
    Columns("M:M").Select
    Selection.TextToColumns Destination:=Range("M1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
        TrailingMinusNumbers:=True
    Columns("N:N").Select
    Selection.TextToColumns Destination:=Range("N1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
    Columns("O:O").Select
    Selection.TextToColumns Destination:=Range("O1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
    Columns("P:P").Select
    Selection.TextToColumns Destination:=Range("P1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
    Columns("Q:Q").Select
    Selection.TextToColumns Destination:=Range("Q1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
        TrailingMinusNumbers:=True
 
End Sub

Open in new window

0
salesanalystspiAuthor Commented:
Unfortunantly the macro will not work because the number of colums before the formulas changes every time the report is ran and I can not attach the macro to the excel spread when it is exported from SSRS. I've looked at the formatting and I have the option to render the data output as an "attribute" or "element". I have tried both but have not been successful. I'm willing to try those again if there is something else that I should have changed but didn't
0
Ardhendu SarangiSr. Project ManagerCommented:
Would you be able to show the steps you follow to pull the report out of SSRS? a few screenshots of how the report generation screen looks?

- Ardhendu
0
salesanalystspiAuthor Commented:
Here are the screen shots you asked for. The last step is an option where you can select export to excel from the menu bar. There are no other settings you can choose when you export it to excel
expexc-screen.doc
0
HadushCommented:
Have you tried your calculation inside the query and create those derived columns? That way you don't have to worry about any formatting and also you will have better performance.
0
salesanalystspiAuthor Commented:
I need to have the calculations also be out side the query so that when the people in the field make changes to the excel file those update. The calculations I am running are not part of the approved formulas that SSRS will export to Excel
0
salesanalystspiAuthor Commented:
On a side note. If I do a find/replace and replace an "=" with an "=" (yes I'm replacing exactly the same thing) it forces a re-calculation and everything works. Maybe that will give someone out there a better idea as to what is happening?
0
Ardhendu SarangiSr. Project ManagerCommented:
Hi,
When you are exporting the data into excel, everything is converted into text and moved out. This is obivously messing up the formulas as they are not meant to be stored as TEXT.

When you are editing anything in the cells, Excel recalculates and then displays the actual value.

I looked at your screenshots and there seems to be one possibility. What is the purpose of the tab marked as Format. Can you change any format of the end-cells over there?

- Ardhendu
0
Ardhendu SarangiSr. Project ManagerCommented:
I did some more digging up at the MSDN forums and it looks like this is an issue which Microsoft still has to fix.  

So currently there is no way to fix this short of editing the cells with the formulas in it.

Pls See attached screenshot and link below.

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/b4b2e5dc-2e6c-4b2e-92e2-a598976c149b


- Ardhendu

p.s - I also learnt something new today. Thanks for the opportunity to answer your question.

Microsoft-Limitation.jpg
0
dotnetchickCommented:
If you are using SSRS 2005, I know of no way that you can produce a working formula using reporting services.
0
salesanalystspiAuthor Commented:
I am using SSRS 2005. What is the solution?
0
dotnetchickCommented:
Like I said, I don't know of any way, short of buying a third party add-on (http://officewriter.softartisans.com/), that SSRS 2005 does it out of the box.
0
salesanalystspiAuthor Commented:
Sorry about that I didn't see the "No" in front of way. I did find a way to get things to work. I passes text strings like the one above that use indirect cell referencing. The only issue I have is that Excel treats the formula like a text string. I then have to go through the hassle of doing a replace all of "=" for "=" (yes I'm replacing it with the same thing) Once I do that excel recalculates everything nicely
0
Ardhendu SarangiSr. Project ManagerCommented:
Hi,

I have created a small tool for you which automates the formula fixing part. Can you take a look at this see if this works for you?

To execute this, click on the button and select the relevant file.

- Ardhendu
Fix-SSRS-Formulas.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
salesanalystspiAuthor Commented:
Yes that does work for the speciffic file. The only problem is that report gets sent out to 10 different people and I would need a way to attach that macro to the report it's self. I'm not good with Macros. Is there a way to code the macro within the speciffic sheet? That would be the best solution short of MS correcting this issue.
0
Ardhendu SarangiSr. Project ManagerCommented:
This macro will work on any report that is created from SSRS and not necessarily on this file. After you download and save your reports from SSRS, just run this macro and select the created file. It should be able to fix all the files.

You do not have to change any code at all. I have made this code generic to work on excel files which has the formulas on any column or cell within a selected spreadsheet. Please test this with a few other files and let me know if this does not work.

- Ardhendu
0
Ardhendu SarangiSr. Project ManagerCommented:
Also if you like, I can automate the emailing part of the file too. So when you create a report and save it, all you need to do is open this macro and click on the button.

1. Excel will prompt you to select the right file.
2. Once the file is selected, the macro will change the text into formulas.
3. It will save the file and you will be prompted to mail this out to the group as well.

See attached code.

- Ardhendu.
Fix-SSRS-Formulas.xls
0
Ardhendu SarangiSr. Project ManagerCommented:
Hi,
Did my solution work for you?

Thanks,
Ardhendu
0
salesanalystspiAuthor Commented:
It might function correctly I don't know I haven't tried it. As for the solution Its not a good one for our organization as we need to have the report distributed through SSRS. I believe this is just something that MS needs to address before a better solution will be found. Thank you for your efforts though
0
Ardhendu SarangiSr. Project ManagerCommented:
you are welcome! Please close this question now.

- Ardhendu
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.