Solved

Text String expression to auto calculate in Excel

Posted on 2009-07-16
24
1,913 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

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
 
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

829 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