Using IF statement with GETPIVOTDATA (1 Viewer)

F

flynjack

New member

Local time

Yesterday, 19:13

Joined

Jul 14, 2008

Messages

7

• Jul 23, 2008

• #1

Hell all;

I have an excel spreadsheet that is linked to a pivot table. The cell will reflect what ever the pivot reference link is behind it.
Here's my problem, if the link doesn't exist (Null) I want the cell to reflect "0" (Zero).
I assumed the following formula work but it doesn't

=IF(=GETPIVOTDATA("SEAT AVAL",'ATRRS PIVOT'!$A$3,"MOS","13D1","FY","2009","QUARTER","Q1") Is Null, "0", =GETPIVOTDATA("SEAT AVAL",'ATRRS PIVOT'!$A$3,"MOS","13D1","FY","2009","QUARTER","Q1"))

Any ideas on how to make this work would be appreciated. Thanks

Flynjack

 

N

namliam

The Mailman - AWF VIP

Local time

Today, 01:13

Joined

Aug 11, 2003

Messages

11,695

• Jul 23, 2008

• #2

Dont use the extra = signs... = means the start of a formula in a cell, but once it is a formula, it says a formaly so you only need one = sign to start.

=IF(GETPIVOTDATA("SEAT AVAL",'ATRRS PIVOT'!$A$3,"MOS","13D1","FY","2009","QUARTER","Q1") Is Null, "0", GETPIVOTDATA("SEAT AVAL",'ATRRS PIVOT'!$A$3,"MOS","13D1","FY","2009","QUARTER","Q1"))

I think that should work.

 

F

flynjack

New member

Local time

Yesterday, 19:13

Joined

Jul 14, 2008

Messages

7

• Jul 23, 2008

• #3

Mailman;

Took out the "=" signs based on your recommendation. Unfotunatley, excel still doesn't like it. Any other ideas?

 

N

namliam

The Mailman - AWF VIP

Local time

Today, 01:13

Joined

Aug 11, 2003

Messages

11,695

• Jul 24, 2008

• #4

I was so bussy tripping over your first problem I didnt notice the second...

"Is null" is a database thing, in excel you use "Isblank (CellReference)"

 

F

flynjack

New member

Local time

Yesterday, 19:13

Joined

Jul 14, 2008

Messages

7

• Jul 24, 2008

• #5

I have tried the Isblank command and it still doesn't work.

 

N

namliam

The Mailman - AWF VIP

Local time

Today, 01:13

Joined

Aug 11, 2003

Messages

11,695

• Jul 24, 2008

• #6

Should work like a charm!

=IF(IsBlank(GETPIVOTDATA("SEAT AVAL",'ATRRS PIVOT'!$A$3,"MOS","13D1","FY","2009","QUARTER","Q1 ")), "0", GETPIVOTDATA("SEAT AVAL",'ATRRS PIVOT'!$A$3,"MOS","13D1","FY","2009","QUARTER","Q1 "))

 

F

flynjack

New member

Local time

Yesterday, 19:13

Joined

Jul 14, 2008

Messages

7

• Jul 24, 2008

• #7

Got it to work with ISERR instead of ISBLANK. Thanks for the help.

 

S

shades

Registered User.

Local time

Yesterday, 18:13

Joined

Mar 25, 2002

Messages

516

• Jul 25, 2008

• #8

flynjack said:

Got it to work with ISERR instead of ISBLANK. Thanks for the help.

Click to expand...

That's good. But be sure everything works as you intend in several scenarios, because in some contexts, ISERR (and other error functions) can obscure future problems.

(Note that ISERROR was the function used in 2000/2002/2003. Did this change with 2007?)
________
Digital scales

 

Last edited: Feb 16, 2011