Hi Frans,

I suspect your GETPIVOTDATA formula has a filter that remains for the "Yes" value, but difficult to tell without seeing the file, or at least a sample file so we can replicate your scenario.

Mynda

Yes of course I'd better make an example, but after trying again now for more than half an hour I quit.

Because of named tables, lot's of vlookups en referencing to headercells, I can't get it work in a simple sheet with only three tabs and four or five rows for an example. Sorry for now, because I really want to know if it's something that can work.

But I'm a bit in a hurry, things have to be ready in some days (first general impression already tomorrow). I think I have a work around for the moment and come back later here, when I've some time, for the real knowledge 😉

Thanks anyway for your reaction Mynda!

Maybe the error message tells you more: it gives the yellow triangle and the hoover over text is something as: "Due to move or delete there is an unvalid cel reference, or the function gives a referral error as result". When I filter the datatable, this message comes in cells depending on the filter.

Somehow I can understand that, but I don't understand that there is 1 group where this occurs also when not filtering the data.

But I can't show you in a file, only with screenshots, but don't think that'll help, does it?

One other thing I thought of (but didn't had the change to verify): can it be that I created my own trouble by first making the get pivotdata formula (which seemed to work) and after that making changes in the pivottable (i.e. addition of the yes/no field)?? (which already was part of the data of course, but maybe changes something for the get pivotdata function?).

Hi Frans,

Your last comment sounds like you're getting close to what the problem may be, which is what I suspected initially.

When you insert a GETPIVOTDATA formula Excel automatically inserts the arguments based on the fields in the PivotTable. Take this one as an example:

=GETPIVOTDATA("Order Amount",$A$4,"Order Date",1,"Years",2009)

I can tell that this formula is returning the Order Amount for the Order Date 1 and Year 2009. If I were to change the PivotTable to remove the Year field the formula would break.

If you're changing your PivotTable so that the Yes/No field is no longer present then your formula will break.

You can learn more about the GETPIVOTDATA function here.

If the above doesn't help then sharing some before and after screenshots may do.

Mynda

Think I solved the problem (at least for this case and this moment). I'll probably come back on it later (after this project ends, end of the month), because I also want to understand if this is the right way to do it.

What I changed in the pivot table: I moved the delta values to the Rowlabel part. After that I moved that 'yes/no' field to the column labels part.

That did it! The function is now working fine. Only thing now is that the Pivot Table is not 'viewable' (if that's an English word (it doesn't have the right presentation), but I can copy it for a workaround version.

Ok. When you come back to it I think you'll need to create a mock-up of the data so we can see your question in context.