Search results

Hi John. You can't do this without some faking. Your only option is probably to snap four individual rectangle shapes to the grid in cells E9:E10 & J9:J10, make them the same color as your Pivot, and manually write in them the headings you want.

MelIvy: My suggestion is to bring all the data into one sheet and use a PivotTable to summarize. Is that a possibility?

Sarfraz: How many sheets do you have in the workbook? And how many used rows and columns are in each sheet? Also, in each sheet, select any cell and push Ctrl + End, and take note of which cell gets selected. This will take you to where Excel thinks your data ends, called the Used Range...

How about adding another column to the source table called 'Blank' and populating it with a Yes or No, and then filtering out any Yes values in the PivotTable itself?

...or if you want to get fancy without macros: http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/

Hi ExcelBeginner20 and welcome to the forum. It would help us answer your question if you could upload a sample file. As RSidhu has pointed out, a PivotTable would certainly let you do this. And in Excel 2010 or later you also have something called Slicers that let you filter a PivotTable...

In that case you can use a version of the trick I outlined in that link above, but instead of using slicers to sync the dropdown (which requires Excel 2010 or later), you use some code. I have some code that I'm in the middle of refreshing that will do the trick. Let me see if I can get it...

Those values won't sum because you don't have the same values in 'Booking Date', 'Booking Rate', 'Company Booking Rate', and 'Company Cost' for the two items. So Excel treats them as two different items. You either need to make the data in those columns exactly the same, or remove those...

Get rid of the line pt.RefreshTable as there's no need for it. (You would only need that if new data was getting added to the PivotTable). What is the layout/orientation of the fields you are filtering? Are they in the ROWS area? Or the COLUMNS area? Or the FILTERS area? If they are in the...

Good idea. Maybe even 'Show Filtered AND Unfiltered", although that's getting a bit long.

So I'm getting close to finishing a pretty cool add-in that does this. It's a cross between a slicer and the existing PivotTable Filter Dialog box. You launch it simply by double clicking a PivotTable Field Column Header, and you get something like this: Note the 'Show Viewable Items'...

Dinesh: that's a pretty massive increase in requirements. That kind of logic can only be handled well by using a Select Case statement within a User Defined Funciton (UDF). In other words, you need a macro. Furthermore, the coding of the macro will be greatly simplified if you can change the...

Well, to be more precise that's 1000 comments, not 1000 posts. But hey...

Phew. That took longer than I thought. No wonder my book is like a year behind schedule.

Whoops, left a condition out. Here's the corrected one. =IF([@Status]="Closed",2,IF([@Delivery]=0,3,IF([@

You can actually use a much simpler IF statement than those above to achieve this. I can't upload a file, but if you change your data into an Excel Table (using the Ctrl T shortcut) then this will do it: =IF([@Status]="Closed",2,IF([@Delivery]=0,3,IF([@

Great. One more question: is there an upper limit on that number? i.e. will there say always be between 0 and say 10 in that field? Or could it be any number?

Okay. In 'No of Items' will the user be typing an actual number, or the text ">0"?

Dinesh: Just to be clear, will there ever be something in both the 'Quote Status' AND the 'No of Items' cells for a particular line at the same time? e.g. Opened, Suspended, DIR, 5 or any of the other combinations I've added in the below image?

@MalR Out of curiosity, what do the different colors represent? Is there any chance you might also want to report on them? If so, it's simple to amend the function that @p45cal has provided to accommodate this, if you'll pardon the pun.

ISG: Your question does not contain enough information. What functionality are you wanting help with? What are you trying to do?

RSidhu: You'll have more luck getting a response if you post a sample file showing what your data layout looks like and what you want to achieve

Dinesh: again as per my earlier post, by far the easiest and most efficient way to do this is via a lookup table. Using a Nested IF statements is simply too complicated. I can help you with a lookup table to fix this once you've answered my question above, but I can't help you with nested IF...

Further to what p45cal says, Excel will actually construct the GETPIVOTDATA formula for you, if you have the Generate GETPIVOTDATA enabled: Simply type an = in the cell where you want your formula, then select the cell in the PivotTable that you want to reference. Excel will then create the...

Yeah, weird.