Prev: Deleting worksheets in a macro
Next: Find value in a Range of Cells and perform Conditional Formatt
From: craig on 28 Jan 2010 23:32
Hi, my question is..I want to use an If formula based on the Grp colomn value
BU (see formula below), but it doesnt recognise the value as "BU", it just
returns the false value (value if false).

Pivot table rows are as follows
SO # Grp Cust Whse SO
311450 BQ BAKERS S 10-Nov-Tue
312385 0 MODERN S 12-Nov-Thu
312403 BU BUNNINGS S 12-Nov-Thu

I have inserted a calculated field with formula
=IF(Grp="BU",0,1)
The formula always returns 1
Thanks for your answer

From: Roger Govier on 29 Jan 2010 05:08
Hi Craig

Add an extra column to your source data headed Test with a formula like
=IF(B2="BU",0,1)
Then expand your source to include the new column.
Drag Test to the area where you want it on the PT - presumably the Data area
--
Regards
Roger Govier

"craig" <craig(a)discussions.microsoft.com> wrote in message
news:C4563D71-AB4C-4317-8B5A-2DE859970B5B(a)microsoft.com...
> Hi, my question is..I want to use an If formula based on the Grp colomn
> value
> BU (see formula below), but it doesnt recognise the value as "BU", it just
> returns the false value (value if false).
>
> Pivot table rows are as follows
> SO # Grp Cust Whse SO
> 311450 BQ BAKERS S 10-Nov-Tue
> 312385 0 MODERN S 12-Nov-Thu
> 312403 BU BUNNINGS S 12-Nov-Thu
>
> I have inserted a calculated field with formula
> =IF(Grp="BU",0,1)
> The formula always returns 1
> Thanks for your answer
>
>
> __________ Information from ESET Smart Security, version of virus
> signature database 4817 (20100129) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>

__________ Information from ESET Smart Security, version of virus signature database 4817 (20100129) __________

The message was checked by ESET Smart Security.

http://www.eset.com



From: craig on 31 Jan 2010 16:50
Hi Roger, Thanks but I didnt reallly want to have to modify my source data.
Is there a reason the pivot table formula wont recognise the text criteria?
Is this a limitation of pivot tables? The formula is simple enough, I cant
understand why it wont work.

"Roger Govier" wrote:

> Hi Craig
>
> Add an extra column to your source data headed Test with a formula like
> =IF(B2="BU",0,1)
> Then expand your source to include the new column.
> Drag Test to the area where you want it on the PT - presumably the Data area
> --
> Regards
> Roger Govier
>
> "craig" <craig(a)discussions.microsoft.com> wrote in message
> news:C4563D71-AB4C-4317-8B5A-2DE859970B5B(a)microsoft.com...
> > Hi, my question is..I want to use an If formula based on the Grp colomn
> > value
> > BU (see formula below), but it doesnt recognise the value as "BU", it just
> > returns the false value (value if false).
> >
> > Pivot table rows are as follows
> > SO # Grp Cust Whse SO
> > 311450 BQ BAKERS S 10-Nov-Tue
> > 312385 0 MODERN S 12-Nov-Thu
> > 312403 BU BUNNINGS S 12-Nov-Thu
> >
> > I have inserted a calculated field with formula
> > =IF(Grp="BU",0,1)
> > The formula always returns 1
> > Thanks for your answer
> >
> >
> > __________ Information from ESET Smart Security, version of virus
> > signature database 4817 (20100129) __________
> >
> > The message was checked by ESET Smart Security.
> >
> > http://www.eset.com
> >
> >
> >
>
> __________ Information from ESET Smart Security, version of virus signature database 4817 (20100129) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>
From: Roger Govier on 31 Jan 2010 18:56
Hi Craig

If you want to send me a sample of your raw data, then I will see if I can
come up with any other solution.
To mail direct
roger at technology4u dot co dot uk
Change the at and dots to make a valid email address.

--
Regards
Roger Govier

"craig" <craig(a)discussions.microsoft.com> wrote in message
news:1B31583A-1C07-4184-BFCF-8B5BA5C5461A(a)microsoft.com...
> Hi Roger, Thanks but I didnt reallly want to have to modify my source
> data.
> Is there a reason the pivot table formula wont recognise the text
> criteria?
> Is this a limitation of pivot tables? The formula is simple enough, I cant
> understand why it wont work.
>
> "Roger Govier" wrote:
>
>> Hi Craig
>>
>> Add an extra column to your source data headed Test with a formula like
>> =IF(B2="BU",0,1)
>> Then expand your source to include the new column.
>> Drag Test to the area where you want it on the PT - presumably the Data
>> area
>> --
>> Regards
>> Roger Govier
>>
>> "craig" <craig(a)discussions.microsoft.com> wrote in message
>> news:C4563D71-AB4C-4317-8B5A-2DE859970B5B(a)microsoft.com...
>> > Hi, my question is..I want to use an If formula based on the Grp colomn
>> > value
>> > BU (see formula below), but it doesnt recognise the value as "BU", it
>> > just
>> > returns the false value (value if false).
>> >
>> > Pivot table rows are as follows
>> > SO # Grp Cust Whse SO
>> > 311450 BQ BAKERS S 10-Nov-Tue
>> > 312385 0 MODERN S 12-Nov-Thu
>> > 312403 BU BUNNINGS S 12-Nov-Thu
>> >
>> > I have inserted a calculated field with formula
>> > =IF(Grp="BU",0,1)
>> > The formula always returns 1
>> > Thanks for your answer
>> >
>> >
>> > __________ Information from ESET Smart Security, version of virus
>> > signature database 4817 (20100129) __________
>> >
>> > The message was checked by ESET Smart Security.
>> >
>> > http://www.eset.com
>> >
>> >
>> >
>>
>> __________ Information from ESET Smart Security, version of virus
>> signature database 4817 (20100129) __________
>>
>> The message was checked by ESET Smart Security.
>>
>> http://www.eset.com
>>
>>
>>
>
> __________ Information from ESET Smart Security, version of virus
> signature database 4822 (20100131) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>

__________ Information from ESET Smart Security, version of virus signature database 4822 (20100131) __________

The message was checked by ESET Smart Security.

http://www.eset.com



From: craig on 31 Jan 2010 21:43
Thanks Roger but I have used your suggestion (sort of) by using a different
field from my data for the if logic test criteria. In summary instead of
using a text field I am using a numeric value field which works fine. It
appears to me that formulas in pivot tables do not like text values as
criteria in the row fields.
Thanks for your help

"Roger Govier" wrote:

> Hi Craig
>
> If you want to send me a sample of your raw data, then I will see if I can
> come up with any other solution.
> To mail direct
> roger at technology4u dot co dot uk
> Change the at and dots to make a valid email address.
>
> --
> Regards
> Roger Govier
>
> "craig" <craig(a)discussions.microsoft.com> wrote in message
> news:1B31583A-1C07-4184-BFCF-8B5BA5C5461A(a)microsoft.com...
> > Hi Roger, Thanks but I didnt reallly want to have to modify my source
> > data.
> > Is there a reason the pivot table formula wont recognise the text
> > criteria?
> > Is this a limitation of pivot tables? The formula is simple enough, I cant
> > understand why it wont work.
> >
> > "Roger Govier" wrote:
> >
> >> Hi Craig
> >>
> >> Add an extra column to your source data headed Test with a formula like
> >> =IF(B2="BU",0,1)
> >> Then expand your source to include the new column.
> >> Drag Test to the area where you want it on the PT - presumably the Data
> >> area
> >> --
> >> Regards
> >> Roger Govier
> >>
> >> "craig" <craig(a)discussions.microsoft.com> wrote in message
> >> news:C4563D71-AB4C-4317-8B5A-2DE859970B5B(a)microsoft.com...
> >> > Hi, my question is..I want to use an If formula based on the Grp colomn
> >> > value
> >> > BU (see formula below), but it doesnt recognise the value as "BU", it
> >> > just
> >> > returns the false value (value if false).
> >> >
> >> > Pivot table rows are as follows
> >> > SO # Grp Cust Whse SO
> >> > 311450 BQ BAKERS S 10-Nov-Tue
> >> > 312385 0 MODERN S 12-Nov-Thu
> >> > 312403 BU BUNNINGS S 12-Nov-Thu
> >> >
> >> > I have inserted a calculated field with formula
> >> > =IF(Grp="BU",0,1)
> >> > The formula always returns 1
> >> > Thanks for your answer
> >> >
> >> >
> >> > __________ Information from ESET Smart Security, version of virus
> >> > signature database 4817 (20100129) __________
> >> >
> >> > The message was checked by ESET Smart Security.
> >> >
> >> > http://www.eset.com
> >> >
> >> >
> >> >
> >>
> >> __________ Information from ESET Smart Security, version of virus
> >> signature database 4817 (20100129) __________
> >>
> >> The message was checked by ESET Smart Security.
> >>
> >> http://www.eset.com
> >>
> >>
> >>
> >
> > __________ Information from ESET Smart Security, version of virus
> > signature database 4822 (20100131) __________
> >
> > The message was checked by ESET Smart Security.
> >
> > http://www.eset.com
> >
> >
> >
>
> __________ Information from ESET Smart Security, version of virus signature database 4822 (20100131) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>
 | 
Pages: 1
Prev: Deleting worksheets in a macro
Next: Find value in a Range of Cells and perform Conditional Formatt