Solved

Text String expression to auto calculate in Excel

Posted on 2009-07-16
24
1,905 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:salesanalystspi
  • 11
  • 10
  • 2
  • +1
24 Comments
 
LVL 20

Expert Comment

by:pari123
ID: 24870863
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
 

Author Comment

by:salesanalystspi
ID: 24870889
How do I change that format from with in SSRS?
0
 
LVL 20

Expert Comment

by:pari123
ID: 24870897
Whats SSRS? sorry i didn't get it...
0
 

Author Comment

by:salesanalystspi
ID: 24870920
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
 
LVL 20

Assisted Solution

by:pari123
pari123 earned 500 total points
ID: 24871001
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
 

Author Comment

by:salesanalystspi
ID: 24871126
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
 
LVL 20

Expert Comment

by:pari123
ID: 24871158
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
 

Author Comment

by:salesanalystspi
ID: 24871260
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
 
LVL 8

Expert Comment

by:Hadush
ID: 24872613
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
 

Author Comment

by:salesanalystspi
ID: 24872665
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
 

Author Comment

by:salesanalystspi
ID: 24872689
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
 
LVL 20

Expert Comment

by:pari123
ID: 24872865
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 20

Expert Comment

by:pari123
ID: 24872926
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
 
LVL 1

Expert Comment

by:dotnetchick
ID: 24875492
If you are using SSRS 2005, I know of no way that you can produce a working formula using reporting services.
0
 

Author Comment

by:salesanalystspi
ID: 24879441
I am using SSRS 2005. What is the solution?
0
 
LVL 1

Expert Comment

by:dotnetchick
ID: 24879490
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
 

Author Comment

by:salesanalystspi
ID: 24879536
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
 
LVL 20

Accepted Solution

by:
pari123 earned 500 total points
ID: 24879936
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
 

Author Comment

by:salesanalystspi
ID: 24880027
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
 
LVL 20

Expert Comment

by:pari123
ID: 24880969
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
 
LVL 20

Expert Comment

by:pari123
ID: 24881127
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
 
LVL 20

Expert Comment

by:pari123
ID: 24899471
Hi,
Did my solution work for you?

Thanks,
Ardhendu
0
 

Author Comment

by:salesanalystspi
ID: 24899521
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
 
LVL 20

Expert Comment

by:pari123
ID: 24901039
you are welcome! Please close this question now.

- Ardhendu
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now