Using Data in Pivot Table for refernce point returns #NAME?

Hello All,

Please see attached sheet:

Excel Help Me2.xlsm

My example sheet i removed the pivot table since it contained employee names.

I have this array formula that works in my example:
{=IF(COUNTIF(P8:AD8,1)=0,"No Worker Categories Found",StringConcat(",",IF(P8:AD8=1,$P$6:$AD$6,"")))}

But then I pop'ed this formula into my primary sheet (where the range of P:AD is part of a pivot table) and is get an error "#NAME?".

Can I not use formulas to look into pivot tables as reference points?

Thanks,

Originally Posted by JasonNeedsHelp

Hello All,

Please see attached sheet:

Attachment 378823

My example sheet i removed the pivot table since it contained employee names.

I have this array formula that works in my example:
{=IF(COUNTIF(P8:AD8,1)=0,"No Worker Categories Found",StringConcat(",",IF(P8:AD8=1,$P$6:$AD$6,"")))}

But then I pop'ed this formula into my primary sheet (where the range of P:AD is part of a pivot table) and is get an error "#NAME?".

Can I not use formulas to look into pivot tables as reference points?

Thanks,

Any ideas? Thanks

Originally Posted by MarvinP

Hi Jason,

I think I know what you are asking. When I try to use a cell in my Pivot Table for a formula somewhere else, it sometimes doesn't work. Try to put a cell just to the right of the Pivot Table and make it simple like: =H8 , where H8 is simply a reflection of what is in the Pivot. Then do your formula off this new cell. If that doesn't work then I guess we need to see a better example of what you mean.

Hi MarvinP,

Yes, the simple =H8 formula works.

Originally Posted by Andy Pope

#NAME? would suggest the workbook does not have access to the code for StringConcat function. Either because the code is not present or macros have not been enabled.

Hi Andy,

You mean the source of the Pivot table? No, that is actually linked to a database on a server. The simple =(to cell) works. I guess i will have to mirror the data?

Also, i was thinking does it need to read the formula? Could i use what is notblank? I tried "<>"&"*" but still get the #NAME? error.

Thanks for the help guys. You are correct; I was missing a UDF (which i know nothing about) that wasn't copied over from the worksheet I had help on. Thanks for identifying this!