Replies

I take it back – it DID work. One of the other criteria was not satisfied on the row I was testing, but once I copied it down the values showed up. Thanks – this is a great help!

I am not positive I have interpreted your suggestion correctly – since it appears the parentheses in your formula do not match up exactly. But once I correct for that I am still getting a 0 (ZERO) for my cell results. Great idea though – and again I may not be interpreting this precisely (see below)

=SUMPRODUCT(–(INDIRECT(Detail_Whse_Region)=$B77),
–(INDIRECT(Detail_Whse_Class)”O”),
–Left(INDIRECT(Detail_Whse_Hospital),3)=”031″,
–INDIRECT(Detail_Proj_Days))

That is a beautiful thing – thank you so much.

Probably a better way, but if date is in A1 formula in A2

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

and copy down

I think I discovered the issue – and it has nothing to do with an Excel 2007 bug.

I have a custom list that contains a complete listing of the location IDs concatenated with the location names. Some of the location names are slightly different on the data source I am using for the pivot table. My guess is Excel attempts to maintain the integrity of the order in a customized list OVER any alpha-numeric sorting – and did the best it could with the mix of data in the pivot. And the reason it the sort worked “correctly” on the Excel 2003 machine – is because that machine did not have the custom list added.

I had to delete the custom list from my Excel 2007, but I did get the sort to work.

Thanks for the tip. I am having lots of issues transitioning to 2007: pivot tables; external connections; AND charts.

I finally tried it with the filename prefixed to the range

=’5-744343-SingleDataSet.xls’!ChartRangeData

And that worked. Did not realize that the filename had to be used – especially if only one workbook is open. Sorry for the wild goose chase.

Yes – it does work. And I see how it’s setup. And I can even duplicate it on my 2003 machine and then open it on my 2007 machine. But still cannot figure out how to create it on the 2007 version.

Is there a reason why this technique would not work for Excel 2007. I have that version (which I do not like) now on one of my computers – and cannot get the offset formula to work. I keep getting a “That function is not valid” or “The formula you typed contains an error…” message. I’ve triple-checked the syntax (and even pasted in Hans’ as a quadruple check).

Of course – you’re probably right.

I think you may be able to actually create the named range, but may not be able to use it in an array formula.

1- Filter to the visible cells desired
2- Highlight/select the cells
3- Press alt + ; (ALT PLUS SEMICOLON or Edit | Goto | Special | VisibleCellsOnly)
4- Type the desired range name in the name box
5- View the result from the Insert | Name | Define window

If you selected non contiguous cells you will see several ranges seperated by commas in the Refer To window

Assuming you replace your uncompleted months’ with 0 instead of ‘0 (zero amount instead of text zero) this should work

Cell AB11 =SUM(OFFSET(P11,0,0,,MATCH(0,$B11:$M11,0)-1))
Cell AP11 =SUM(OFFSET(AD11,0,0,,MATCH(0,$B11:$M11,0)-1))

I take so much from this site…glad to be able to contribute something

Is this what you meant?

Private Sub Workbook_Open()
    With Worksheets("Sheet1")
        .Unprotect Password:="password"
        .EnableOutlining = True
        .Protect UserInterfaceOnly:=True, Password:="password"
    End With
End Sub

Figured it out…the path name needed to be changed to..

sharepoint.mycompany.comsitesteamsiteDWOR Upload