mswin

Hi,
Is it possible to combine 2 defined names and give them a single name.
In my excel sheet I have a defined named paramter and also have a chart named Chart1.
I want to define a name by comining these 2 named ranges.
So that I want to publish bith to a sinle Excel web access webpart in sharepoint.
How can I do that
Suppose name for the first is MyFilter and Name for the Chart is Chart1, How to specify the formula for newly created named range;In the refers to section of Define Name in Excel what should I give Is this the corect one ="MyFilterEmbarrassedChart 1"


Any help in this regard will be highly appreciated.

Regards
mswin



Re: SharePoint - Excel Services Excel - Define name combining 2 Names

Sanjay Kulkarni

In Excel Client select a range that includes both the parameter cell as well as the chart using the standard Excel selection mechanisms (Shift+Click or CTRL+Click). Once you have selected these; then right click and select "Name a Range" and create a new named range "MyChartAndFilter". While publishing to Excel Services or while configuring in EWA you can select this new named range like any other named range in the workbook.

One caveat is that this only works if the paramter cell and chart are in the adjoining area (the range should be contigeous). Meaning if the parameter is in one corner and the chart in the other and you select using CTRL+ click then it will not work.

Hope this helps,

-Sanjay.





Re: SharePoint - Excel Services Excel - Define name combining 2 Names

mswin

Hi Sanjay,

Even I place them adjacent to each other the chart and filter, I am not able to select both the things and define the name. Ctrl + Click / Shift + Click is not working with chart and the filter I defined. Is there any other alternative.Is there any other way to define a name for these 2 defined names.

Thanks,
mswin





Re: SharePoint - Excel Services Excel - Define name combining 2 Names

Sanjay Kulkarni

In Excel Client select the filter and the chart; then on the Formula Tab use Define Name. The dialog box that comes up also has a control for selcting the range if you need to make changes.

-Sanjay.





Re: SharePoint - Excel Services Excel - Define name combining 2 Names

Danyh

You need to define the name based on the range the chart is covering and not the chart itself. The range need to be a simple range.

If your filter is in G1 and the chart covers H2:M20 you need to define a name with =$G$1EmbarrassedM$20 and not =$G$1,$H2EmbarrassedM20

Dany





Re: SharePoint - Excel Services Excel - Define name combining 2 Names

mswin

Hi,

Thanks for all the replies.

Case 1:

I am having Pivot table and chart in the same sheet.
This is how I have given the value for reference to section in Define Name window.
(=$G$1EmbarrassedM$20 )
I have published the whole workbook to Excel services.I am not adding any parameters while publishing workbook.
And trying to display my named item (Filter + Pivot chart) in ExcelWebAccess webpart.
But it seems to be not working and giving an error "Named Item can not be displayed".
How to publish that named range to Excel services.

Case 2: I have moved Pivot chart to another sheet, as Pivot table is getting scatterred with huge data. In this case The filter will be in one sheet and Chart will be on another sheet. Even in this case, I want to show the filter and Chart together as named item in Excel services, is there a way to do that, instead of Adding filter webpart for the each of my Excel report filter.I am adding a named item like this:

Sheet 1 has filter and Pivot table, Sheet 2 has Pivotchart.

=Sheet1!$A$1EmbarrassedB$1Tongue Tiedheet2!$W$15

But still I am getting the same error as Case1.

Any help further will be appreciated.

Regards,
mswin