I am trying to apply a value filter in a pivot table, but the options are

greyed out. Why could this be.

The scenerio, I have a huge file, with client data. I want to see this data

broken down 1st by Sales Manager, 2nd by client. I can get this far. Now I

would only like to see data for "Total" sales over 100K. The value filter is

not available.

greyed out. Why could this be.

The scenerio, I have a huge file, with client data. I want to see this data

broken down 1st by Sales Manager, 2nd by client. I can get this far. Now I

would only like to see data for "Total" sales over 100K. The value filter is

not available.

- How can I hide zero value lines in a pivot table?
- Place a number filter on a report filter in a pivot table
- How can I hide zero value lines in a pivot table?
- Date filter in a pivot table
- Selecting multiple filters in a pivot table
- Apply filter in a pivot table
- Filtering on empty cells in a pivot table
- Apply a filter in a pivot table and extract results in a table
- How do I repeat field value in a pivot table?
- Counting the number of Selected Fields in a Pivot Table Filter
- Exclude zero values using average function in a pivot table
- Show values only in a pivot table
- How do I repeat field value in a pivot table?
- Accumulating Values in a Pivot table
- Conditional formatting in a pivot table
- Lookup in a pivot table
- Conditional formatting in a pivot table
- Calculated field in a pivot table
- OFFSET() not working within a SUMPRODUCT() when refering to a range in a pivot table
- Changing cell text in a pivot table
- Calculated Field in a pivot table
- Not generating/displaying empty cells in a pivot table
- Grouping numbers in a pivot table?
- Automatically sum for specific lines in a pivot table

100101 CCRRENCY & COIN - TELLER #1 11,892.59

100102 CCRRENCY & COIN - TELLER #2 14,850.55

100103 CURRENCY & COIN - TELLER #3 10,247.04

100104 CURRENCY & COIN - TELLER #4 -

In the above pivot table consisting of 3 columns and 5 rows how could I

automatically hide row 5 and all the other $0 value rows in my pivot table?

Thanks!

For example:

I only want to count up the data when the revenue is above a certain threshold.

Is there anyone to have a report filter not just be the multi-select filter?

Reuben

100101 CCRRENCY & COIN - TELLER #1 11,892.59

100102 CCRRENCY & COIN - TELLER #2 14,850.55

100103 CURRENCY & COIN - TELLER #3 10,247.04

100104 CURRENCY & COIN - TELLER #4 -

In the above pivot table consisting of 3 columns and 5 rows how could I

automatically hide row 5 and all the other $0 value rows in my pivot table?

Thanks!

the original data column of dates as dates and have the dates as the only

entry in my column data. Sometimes when I create the pivot table from new

data, I cannot filter the column data with date filters, it only allows me to

filter them as values. Even after I have gone into Pivot Table, Options,

Field Settings and changed the number formate to date and refreshed the Piot

Table, I still cannot filter using date filters (it is greyed out).

values on which to filter. There is a little white box next to each item

that I can select by putting a check mark in it. However, when I create a

pivot table, I can only select one item from the filter at a time. How do

you set up the pivot table so you can select multiple items?

Thanks.

--

Anne

.PivotFields("VendoLoc").PivotItems("x1").Visible = False

.PivotFields("VendoLoc").PivotItems("x3").Visible = False

.PivotFields("VendoLoc").PivotItems("x5").Visible = False

When i really want to see only the X2 and X4 values in the pivot table, so when i change the data the items i still wanna see the X2 & X4 values but now i also got new X6 values and so on to filter, i don't know how to use this code that excel creates in order to filter only the values i wanna see,not the one i dont wan't want to see.

Ideas to solve this 2 issues?

Thanks in advanced.

I am linked to a SQL table where we use a field to determine if an object is unique to a certain customer or for general public use. The SQL table is designed so that the customers number is put in this field and if it is blank then it is considered an item open to the public. I am trying to work in a pivot table and it is showing a blank area in that field on the drop down for the filter but it will not let me select it. I have tried in the search box typing "", "(blank)" and "NULL" all to no avail. Any ideas since I can't change the core programing in SQL other than bringing the data into excel and adding qualifying fields?

I have made a pivot table and I dlike to identify with a macro the documents with net value over 1000. Then extract these values next to the respective sales documents in an are near the pivot table somewhere. The fields are called Document and Sum of Net value. Of course the pivot is very variable one time it has 3000 records and another 5000. So help now is appreciated

thanks

investigation purpose (needing for example a sort on the field) I would like

to have this value repeated on each line. How could I do this?

I've created a pivot table that shows YTD information. The YTD date range is generated by selecting multiple months in a pivot table filter.

I have some formulas outside of the pivot table that are dependant upon how many months of data are being displayed.

In the filter, when more than one month is selected, is diplays "Multiple Items."

My question is, is there a way to count the number of months that are selected in the Month filter?

including the blank cells give me an incorrect average value

the master database each month. To ensure that all figures are included

every month I need to include all combinations (accounts, Cost Centers

etc). Several of this combinations might be zero, and it it also shows

zero in the Pivot table. This makes the pivot table big and filled with

a lot of useless information. Is it possible in a pivottable to only

show figures with a value different from zero?

Thanks for your help

Brgds

Knut

investigation purpose (needing for example a sort on the field) I would like

to have this value repeated on each line. How could I do this?

Does anybody of you know, how I can show in a Pivot table

accumulated values (example)in each row:

Day Amount Accum. Value

1 50 50

3 75 125

8 105 230

.. .. ..

.. .. ..

and so on.

Maybe someone give me a tip.

Thanx a lot

Regards Kurt

I am using conditional formatting in a pivot table to highlight rows where the value in the last column meets a particular criterion.

However everytime I update the table the formatting gets lost.

Can anyone point me in the right direction please?

For example, on the "Main Sheet" it would pull the "Total Qty Ordered" of that drawing type (e.g. WON = 1000, Drawing Number = a and then it should lookup the Total Qty on Order to be 5)

So it's like it's looking up "WON" then looking up "Drawing Number", all in one go from a single source.

Possible?

Thanks

Darryl

let's suppose i have a value in a cell and i want to use that value to

create calculated field in a pivot table,

how can'I put the value of this cell in a variable which can be used in

a pivot table?

thanks folks.

I am having a problem where I'm using 2 OFFSET() functions within a SUMPRODUCT() function. The first offset is working and refers to a range of 4 cells immediately above the cell I'm entering the formula in.

The second OFFSET() is not working. It refers to a range of 4 cells a calculated number of columns to left of the one I'm entering the formula in. The calculated column happens to be a Data column in a pivot table (there is no "Columns" field in the pivot table, so this column is just a Totals column).

When I step through this formula with the "Evaluate Formula" Formula Auditing Tool, the second OFFSET() returns {#VALUE!} error. If I isolate the problematic OFFSET() function and then wrap a SUM() function around it, it returns the correct value.

Does anyone know what is going on here? Is what I'm trying to do possible? Is there just some small error in my syntax?

Here are the formulas:

This is the non-working formula:

=IF(AND($AA9"",$AB9""),SUMPRODUCT((LoanDetail! $R$2:$R$998=$F9)*(LoanDetail!$E$2:$E$998)*(LoanDet ail!$F$2:$F$998))/$G9,IF(AND($AA9="",$AB9""),SUMPRODUCT(OFFSET(M9,-$AB9,0,$AB9,1),OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))/$G9,""))

(For those of you who can see it, I've bolded the problematic OFFSET()function.)

And here is the working formula:

=SUM(OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))

(Notice that it is the exact same OFFSET() isolated then a SUM() wrapped around it.)

Thanks for any help anyone can provide,

Conan Kelly

column that have the same value, blank, etc. Is it possible to turn that

feature off so that it only changes that cell? Also, is there a way to

report that change back to the initial data source? Thanks.

Thanks!

Also posted at ozgrid. Added by mod

I have a question about how to not generate/display cells with no values in a pivot table.

I have written a VBA macro to auto-generate a pivot table based on some data in a worksheet.

I have noticed that by default, in the Total column of the table, cells that are blank are displayed as the null string.

I know that I can replace the null string with 0 or any other value I want via the setting "PivotTable Options->For empty cells, show"

However, I would like to not display the row at all if the cell value is blank.

How can I accomplish this programmatically in my macro?

I am using Excel 2003.

Thanks in advance for your help, and please let me know if you require any further info from me.

I'm trying to group a numeric field in a pivot table so that something like:

Credits earned

10 11 13 15 20 21 25

becomes

Credits earned

10-15 16-20 21-25

But when I select the numeric values in the credits earned field and try to group them it just creates a new group called "Group 1". I've run into this problem before intermittently and sometimes I can get the grouping to work and sometimes not. I've made sure there are no blanks or text values in the field values, so I'm at a loss here... help is appreciated!

I'm using Excel 2003 on Windows XP if that helps.

Thanks!

Orville

I'm not a heavy user of Excels pivot table function so I hope one of the gurus overhere could help me out.

I would like to know how I can sum different lines in a pivot table automatically if they do belong to a certain variable (year). And when there will be a new "location" in the future then it should pick that one up as well.

I have added a file with en example that is pasted as "values" the data behind the pivot is personal.

Thanks in advance for your help.

Ivo