Improper Drill-down format

[Jarlisle]

I have a file where I have a PowerPivot pivot table in it and some formulas to gather data from other sheets and tables in the file (all formulas currently are SUMIFS, IF and some OFFSET inside the SUMIFS). When I right click on any cell with a formula I get a dialog box with the title "Improper Drill-down format" and the message reads "Improper Drill-down format, please remove any embedded functions using addition operators." When I click "OK" nothing happens and it's as if I can use the file normally.

Anyone know what this means?

Thanks

[SamT]

Can you post the functions that are giving you headaches along with their code?

Please use the VBA button to put the code inside VBA tags, so it will be nicely formatted.

[Jarlisle]

Here is an example of the formulas:

=IF($D6="Grand Total",SUM(G$4:G5),SUMIFS(OFFSET('$$$ STIR'!$A$1,0,MATCH(G$3,'$$$ STIR'!$4:$4,0)-1,COUNTA('$$$ STIR'!$A:$A)+5,1),OFFSET('$$$ STIR'!$A$1,0,0,COUNTA('$$$ STIR'!$A:$A)+5,1),$D6))

It should be pretty basic. The formula just totals some ticket sales and the offsets are in there because it pulls from a Pivot table and will adjust the size of the formula based on the size of the pivot table.

You asked for VBA code, but I don't have any in this particular file. The PivotTables in the file are driven through PowerPivot though, so I don't know if that's what is causing it or not.

[SamT]

Jarlisle,

My Office XP Excel like the formula. I don't know what to tell you, except there might be a conflict with some formula elsewhere.

On the Excel menu >> Tool >> Options >> View tab >> Window Options section, put a check in Formulas. Then back on the Menu >> View >> ToolBars, Select Formula Auditing.

If you have a Ribbon Menu...

I would make one suggestion to improve the readability: I notice some Range Constants.
[vba]'$$$ STIR'!$A$1
'$$$ STIR'!$4:$4
'$$$ STIR'!$A:$A[/vba] Name those Ranges with meaningful names. In the Define Name's NameBox, prefix the meaningful part of the name with "$$$ STIR!' with single quotes so as to limit them to Sheet $$$ STIR.

Then replace those references in formulas to the Defined Name.

Repeat for all formulas.