Solved

Text String expression to auto calculate in Excel

Posted on 2009-07-16
24
1,918 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 10
  • 2
  • +1
24 Comments
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
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:Ardhendu Sarangi
ID: 24870897
Whats SSRS? sorry i didn't get it...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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:Ardhendu Sarangi
Ardhendu Sarangi 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:Ardhendu Sarangi
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:Ardhendu Sarangi
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:Ardhendu Sarangi
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:
Ardhendu Sarangi 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:Ardhendu Sarangi
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:Ardhendu Sarangi
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:Ardhendu Sarangi
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:Ardhendu Sarangi
ID: 24901039
you are welcome! Please close this question now.

- Ardhendu
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

733 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