Getdata formula doesn't work with 2 fields of criteria

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3146266%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EGetdata%20formula%20doesn't%20work%20with%202%20fields%20of%20criteria%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3146266%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EHello%2C%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3BI%20am%20trying%20to%20use%20a%20Getdata%20formula%20that%20works%20when%20I%20use%201%20field%20of%20criteria%20(%22Size%20Name%22)%20but%20when%20I%20add%20a%20second%20field%20%22Variant%22%20it%20returns%20a%20%23REF!.%20Below%20is%20a%20list%20of%20things%20I%20have%20tried%20but%20haven't%20worked.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E1.%20Change%26nbsp%3B%22'DATA%20BASE'!B33%22%20to%2020%20(This%20is%20the%20value%20in%20the%20cell)%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E2.%20Change%20formatting%20on%20my%20data%20to%20match%20the%20pivot%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E3.%20Change%20all%20formatting%20to%20general.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E4.%20Added%20and%20removed%20%22Size%20Name%22%20and%20%22Variant%22%20from%20Pivot%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E5.%20Tried%20the%20formula%20just%20with%20%22%22Variant%22%2C20%22%20and%20with%20%22%22Variant%22%2C'DATA%20BASE'!B33%22%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20am%20at%20a%20loss.%20Any%20help%20would%20be%20appreciated.%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3ERy%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3CPRE%20class%3D%22%5C%26quot%3Blia-code-sample%22%20language-excel-formula%3D%22%22%3E%3CCODE%3E%3DGETPIVOTDATA(%5C%22Sum%20of%2001%2F2022%5C%22%2C%5C'PM%20Only%20Pivot%5C'!%24A%249%2C%5C%22Size%20Name%5C%22%2C%24C23%2C%5C%22Variant%5C%22%2C%5C'DATA%20BASE%5C'!B33)%26lt%3B%5C%2Fcode%26gt%3B%26lt%3B%5C%2Fpre%26gt%3B%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3146266%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
New Contributor

Hello,

   I am trying to use a Getdata formula that works when I use 1 field of criteria ("Size Name") but when I add a second field "Variant" it returns a #REF!. Below is a list of things I have tried but haven't worked.

 

1. Change "'DATA BASE'!B33" to 20 (This is the value in the cell)

2. Change formatting on my data to match the pivot

3. Change all formatting to general.

4. Added and removed "Size Name" and "Variant" from Pivot

5. Tried the formula just with ""Variant",20" and with ""Variant",'DATA BASE'!B33"

 

I am at a loss. Any help would be appreciated. 

 

Thanks 

Ry

 

 

=GETPIVOTDATA("Sum of 01/2022",'PM Only Pivot'!$A$9,"Size Name",$C23,"Variant",'DATA BASE'!B33)

 

 

 

3 Replies

Hi @ryanpayne ,

 

if the cell B33 contains numbers, try this one:

 

=GETPIVOTDATA("Sum of 01/2022",'PM Only Pivot'!$A$9,"Size Name",$C23,"Variant",TEXT('DATA BASE'!B33,"#"))

Hi @Martin_Weiss ,

I tried your suggestion and it still returns #REF!

Hi @ryanpayne 

 

is this second criteria really visible in the Pivot table?

GETPIVOTDATA can refer only to values that are currently visible.