.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Sum of Specific Named Columns in Excel

Posted By:      Posted Date: October 23, 2010    Points: 0   Category :Sql Server


i want to create aformula in excell  . i have a scenario like that i have one row in which i am giving names to columns like A,B C, etc

I want to sum those columns in which i have given the name 'A' only.

Help Required..





View Complete Post

More Related Resource Links

Sharepoint export to spreadsheet gives two extra columns in excel 2007 named as type and path, is th

When i export a list in excel 2007, it creates two extra columns named as type and path. At the same time this behaviour is not exhibited in office 2000. Is there any way to remove these two columns without using code ? Is there any configuration settings required for this, to remove it on the fly ? Thanks Ravish Verma

how to Freeze specific columns in GridView

My question is how to freeze specifi columns of Gridview control ?Freeze means...for example say if we are freezing the first column of grid then on scrolling a horizontal scroll bar of the grid will scroll all columns other than the first column. That means the first column will be visible always without any movement. But the first column should move if we use the vertical scroll bar. thanks is advance satish  

Document Library "export to spreadsheet" doesn't export all columns in Excel 2003

Hi, we are working with MOSS 2007, Infopath Forms Services, KPIs and Excel 2003. There is a view for some data of the Infopath forms which has to be exported to Excel by our users, but some columns are not exported - without any error message. If I try to export it to Excel 2007 everything is exported as expected, but our users have to use Excel 2003. Any idea?

Insert the results of a stored procedure into specific columns

Hello, I'm trying to update a table with values from a stored procedure. Here is what I've got: Open My_Cursor DECLARE @username varchar(200), @add1 varchar(200), @add2 varchar(200), @city varchar(200), @st varchar(200), @zip varchar(200) Fetch NEXT FROM MY_Cursor INTO @username WHILE @@FETCH_STATUS = 0 BEGIN insert into sheet1$ (f19,f20,f21,f22,f23) EXEC [dbo].[GetPrimaryEEsAddress] @dependentUsername = @username WHERE username = @username FETCH NEXT FROM MY_Cursor INTO @username END Getting error: Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'WHERE'.

Populate Excel Spreadsheet Starting At Specific Row

I am trying to populate an excel workbook using a data flow and it no longer works. I have a command button in cell a1, so the column headings need to start in row 3 and the data in row 4. also the column headings need to be red background. the data is ending up in the right row but no all the rows are also red. everything was working fine, now it is broken and cant figure out why. first i DROP TABLE, to clear the contents of the worksheet, this now only clears the column headings, the i CREATE TABLE, to put back the column headings, this works. Then using the data flow I populate the spreadsheet, it end up in row 3, which is right, but honetly i dnot know why. the problem is all the data rows are red. can anyone please help me with this. thanks.

Want to show FormView in Edit mode with specific table columns depending on their Genre??

hi, I have  a table in my database called "Profiles". This holds data on Female and Male personal characteristics. On my aspx, I have a formview in edit mode which is binded to an object data source with a GetDataByUserId Select method which retrieves data from the "Profiles" Table via a DAL. The "Profiles" table has some columns which relates to females only and some just males and some are generic to both. See example below     UserId - / of Type GUID GenreId /  of Type BOOL. Hip Size (For females) Dress Size ( For Females) ChestSize (For Men ) TrouserSize (For Men) Weight ( For both )   When the User logs in , they go to an update Page which has the Formview in Edit Item Mode and contains drop down Lists which are binded to the Users Selected Value. The DDL are in turn bound to their related table such as HIP, Dress, Chest, Trouser etc. Objective If a female user logs in I just want to show them the (Hip Size) DDL  and DressSizeDDL in the Edit item template of the formview and not the ChestSize DDL or  TrouserSize DDL. At present the GetProfileByUserId returns all colums from the table My question is how do I do this and just show DDL which are relevant to the Female or Male Only. In some cases the user may not have selected an item in the previous form so they could also be

How to read an Excel file and show data from 2 columns

Hi,I have an Excel file called Products.xls .I have Columns A and B, with the titles NAME and QUANTITY.The name of the sheet is SHEET1.The file has about 40 lines.How do i show these data on a Gridview or Listview ?Thank you.

Named Set working in excel but not in Browser....



I have a problem in the named sets.

I have a dimension with 6 levels and I have to calculate the standard deviation for each level, only for the siblings. I used Siblings to calculate the stdevp but it was not working with the filter, So i had to use named sets.

The problem is, this calculation is working fine in the Excel, even with the filter, but in the SSAS browser, filter is not working. Could anyone please suggest me the solution for this, or to calculate stdevp for siblings with some other way, that should work for the filters also(Filter here should be on the same dimension for which the stdevp is being calculated)?.

here are my calculations:

Named Sets:


[LEVEL2] : [DIM_A].[A].[LEVEL2].

Need Search to bring back metadata for Specific Columns


I have been looking at the Search facility in SharePoint Online 2007, as the standard out of the box is no good for me.

I have modified the xsl to make it look pretty, but now i need to work with the metadata so that it is bringing back the column information for my document library when i search using a keyword, for example If i have a column called 'Category' i want this to be brought back in the metadata of the search results.

I am using SharePoint online 2007 so any tips would be great, I am right in thinking i would need to go down the content type and custom scope route?


Merge Columns Like Excel In Gridview


Hello everybody.

I have a excel file which is in structure a class routine:

                  | 9-10 | 10-11 | 11-12 | 12-01 | 01-02 | 02-03 | 03-04 | 04-05 |

tue             |           subject1                      |     subject2     |    subject3      |

wed            |   subject2     |                subject3                   |    subject1      |

thu             |            subject3        |     subject2     |             subject1          

SSIS cannot find excel columns when using SQL command to import

I'm trying to import an Excel file, but receive the following error during the process:

Validation is completed
[DTS.Pipeline] Information: Prepare for Execute phase is beginning.
Progress: Prepare for Execute - 0 percent complete
Progress: Prepare for Execute - 33 percent complete
Progress: Prepare for Execute - 66 percent complete
Progress: Prepare for Execute - 100 percent complete
[DTS.Pipeline] Information: Pre-Execute phase is beginning.
Progress: Pre-Execute - 0 percent complete
[Source - Individuals$ [1]] Error: Column "Last Name 2" cannot be found at the datasource.
[DTS.Pipeline] Error: component "Source - Individuals$" (1) failed the pre-execute phase and returned error code 0xC0202005.

My Excel Source is using "SQL command" data access mode:
Select * from [Members$]

Is there something that I'm missing when using the 'SQL command' mode? Thanks in advance for any help.

Problem with List Columns in Foundation 2010 when Importing from Excel


This is a pretty detailed issue. I will give you all the steps I am doing and hopefully there is a solution.

I recently updated our intranet site from WSS 3.0 to SharePoint Foundation. In WSS 3.0 I added a few of the application templates to use, including Asset Tracking. When I moved to Foundation I decided not to use those (and not sure if I can anyway) and exported the Asset Tracking data to Excel, then imported it to Sharepoint Foundation as a list.

Here is the issue I am having: when I imported from Excel all but one of the columns was created as "multiple lines of text". This means I cannot sort by these columns. I thought I could create similar columns as single lines of text, export back to Excel, copy the data, and import back. This changes the columns again to multiple lines. I even tried just typing in the data as a test and same problem. As long as there is no data, the columns will remain as single line of text; but as soon as I put data in I get this issue.

I can cofirm that these columns do not have more text than should be available in a single line. Any ideas of what I can do to make this work? I am at my wits end on this.

Thanks for any and all help.

Named Set Subtotal for Excel


I am trying to create a named set that will exclude certain members from a dimension. Unfortunately, when that set is used in excel, it does not return a subtotal which is expected behavior. Teo Latchev has blogged about this  as well as posted on this topic with a workaround however it is not working for me...when I include the [All] member, the total it provides is for all members, while I need it to be the sum of the listed members only ...I have tried a couple of tacks with the same result:

select {Measures.[Units]} on 0,
-{[Department].[Department].&[-1], [Department].[Department].&[-2],
[Department].[Department].&[19], [Department].[Department].&[20],
[Department].[Department].&[23], [Department].[Department].&[24],
[Department].[Department].&[25], [Department].[Department].&[27]}}
on 1
FROM [cube]

OR explicitly listing them...

select {Measures.[Units]} on 0,
{      [Department].[Department].&[1]

columns order reverses in matrix when exported to excel


Hi eb

I haver a simple matrix with header that holds description for every column

in matrix. Lets say header looks like:

col1Desc col2Desc col3Desc.

My problem: when report is exported to pdf or excel columns order reverses! so now it is:col3Desc col2Desc col1Desc

The only workaround i found to keep columns headres in place was to place header in a table on top of matrix.

This is fine but now matrix itself has no header so i can't display the columns header in every page of the report just on the first one..

I would very much appriciate a hint for solving any of these problems

either prevent columns reverse in matrix header when report is exported

or some how force the column to appear on top of each page

when contained in a separate table.

Thanks alot!




Excel spreadsheet properties push to sharepoint library columns


Hope this is the right forum; if not, please direct me to the right one.

In Excel 2007 I am filling in a document property programmatically in an Excel 2003 spreadsheet, and creating/filling a custom one.  Just FYI, I use the following VBA code:

    ActiveWorkbook.BuiltinDocumentProperties("Company") = "TEST COMPANY NAME"
    ActiveWorkbook.CustomDocumentProperties.Add "Manager's Name", False, msoPropertyTypeString, "TEST MANAGER NAME"

Then I save the document to a SharePoint library.  Right now it is saved as a 2003 version spreadsheet (.xls).  There are a Company column and a Manager's Name column in the SharePoint library, and Microsoft's online documentation says the properties should populate the corresponding columns in the library.  But they don't!

That's my problem--getting them to populate.  Incidentally, thinking that the spreadsheet version was the problem, I have tried saving these documents both as 2003 spreadsheets and 2007 spreadsheets in my code, but the SharePoint columns never populate either way.  The only way I can do it is by manually filling in the document property from the UI.

Does anyone know what I'm doing wrong, either in Excel or SharePoint or somewhere in between, to cause this?  Is there a non-code solution to the problem?  I can do some C# code if I h

How to read excel in C# with custom columns

I am reading read excel file in C# like

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + txtPath.Text.Trim() + "';Excel 8.0;HDR=YES;";

        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

        DbDataAdapter adapter = factory.CreateDataAdapter();

        DbCommand selectCommand = factory.CreateCommand();
        selectCommand.CommandText = "SELECT * FROM [MSDN$]";

        DbConnection connection = factory.CreateConnection();
        connection.ConnectionString = connectionString;

        selectCommand.Connection = connection;

        adapter.SelectCommand = selectCommand;

        DataSet cities = new DataSet();


        dataGridView1.DataSource = cities.Tables[0];

But above code is not reading correct values in case of cus

Using Excel Source with variable number of columns



I want periodically to insert data from an excel file into a relational table using a SSIS package. However, each time the number of columns in excel may vary. For example, the 1st time the excel file may have 100 rows the second 105 rows etc.

Can this be done in SSIS and if yes what is the solution?


Thanks in advance,


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend