Adding formula columns in excel

Question

Post reply

Adding formula columns in excel

Syed Mehroz Alam

SSChasing Mays

Points: 611
More actions
October 2, 2008 at 8:25 am

#131569

Is there anyway to create formula columns in Excel. I googled but could not find any such thing. Any luck for me?
Regards,
Syed Mehroz Alam
Syed Mehroz Alam
MyBlog | MyArticles

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

vasaharshit Say Hey Kid Points: 698 More actions · Answer 1

Are you talking abt generating formulas from db?

If no you can do that by using macros in excel.

Syed Mehroz Alam SSChasing Mays Points: 611 More actions · Answer 2

Hmmm. I think I will have to do my calculations in the SQL query and just write the result set to excel.

Syed Mehroz Alam
MyBlog | MyArticles

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

You can create a formula column in Excel, using Excel. Open Excel, select the column into which you want the formula. Click on the first row that will contain data (Skipping over any row that has say heading information) with the cursor in the selected cell input an = (equal) sign, then click on the first column to included in the formula, then click the math symbol, in my example the + sign, then click the next column (that is the column that is to be added to the first column selected). When you have completed the formula, depress the return key. My example the fromula is in column 4. What I want in column 4 is the sum of values in column 1 + value in column 3.

The formula is:

=RC[-3]+RC[-1].

Another example: multipling a column by a fixed value would be:

=RC[-3]*2

Then click once again on the cell with the formula, the cell will be highlighted and in the lower right corner will be a "+" sign, click on the + and drag down the Excel column, the formula will then be repeated in each row. Save the Excel workbook. Export to the saved Excel file.

If you can do the math using T-SQL and export the result I think you would be better off.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Syed Mehroz Alam SSChasing Mays Points: 611 More actions · Answer 4

Thanks for your detailed reply. Its a nice idea to write formulae columns before populating the excel sheet so that the calculated columns give the correct values once the data is populated by SSIS. However, there is one problem with my scenario; I want my SSIS task to overwrite any existing data in the excel sheet. Since I could not find how to overwrite data, so I just created a new worksheet in my SSIS task (by executing an SQL's create table command on my excel destination). Since the target worksheet itself is generated by my SSIS package so I will not be able to write the formula columns in advance. Any ideas on this.

Thanks.

Syed Mehroz Alam

Syed Mehroz Alam
MyBlog | MyArticles

vasaharshit Say Hey Kid Points: 698 More actions · Answer 5

okkk!!!

What u can do is create a sheet with all the formulas and put it at a particular place say d:/temp/myexcel1.xls , before transgerring data from ssis just copy that file at destination folder d:/destination/myexcel.xls

Now transfer data in d:/destination/myexcel.xls

so all ur formulas will work....

To copy the file u can creat a batch file and execute it from ssis.

Syed Mehroz Alam SSChasing Mays Points: 611 More actions · Answer 6

Thanks for your reply. I already tried this but when I execute the SSIS task, it starts populating the excel rows after the last row in which I pre-wrote the formula columns. e.g. Lets say our SSIS task populates values in columns A and B and I pre-entered the formula expressions in column C for rows 1-100, then upon execution of the SSIS task, the rows are populated from row 101. Any solution to this?

Syed Mehroz Alam
MyBlog | MyArticles

catogonzalez SSC Journeyman Points: 81 More actions · Answer 7

My solution would be to create a 'template' excel workbook with an empty destination sheet where I only have the columns that will be written to by SSIS. In that workbook I would implement the OnWorkOpen event handler in Excel VBA:

Private Sub Workbook_Open()

Call DoWorkbookOpenTasks

End Sub

... and on the procedure DoWorkbookOpenTasks I would populate the required cells with the formulas. I am currently working on something very similar: let me know If I would further help.

Cato

Phil Parkin SSC Guru Points: 246941 More actions · Answer 8

catogonzalez (5/15/2009)
My solution would be to create a 'template' excel workbook with an empty destination sheet where I only have the columns that will be written to by SSIS. In that workbook I would implement the OnWorkOpen event handler in Excel VBA:
Private Sub Workbook_Open()
Call DoWorkbookOpenTasks
End Sub
... and on the procedure DoWorkbookOpenTasks I would populate the required cells with the formulas. I am currently working on something very similar: let me know If I would further help.
Cato

Nice idea!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin