Recent content by Twee

Hi all, I created a relativly simple template that will update values based on what hospitals the users pick, driven by lots of vlookup and match/index functions. The data displayed is in pretty text boxes (which references a cell; I wanted to make it look prettier with the use of text...

Thanks, Hui!! I still get the report filter on top in my printout but I'll figure out something. Your help really makes me think that I need to practice the offsset function more. Thanks! Twee

Thanks, Hui. I am pretty sure I made the right change but glad to e-mail it to you. Thank you so much!! Just sent it to you.

Hi Hui, Thanks for the clarification. For some reason, I couldn't get the printing correct. I named the Print_Area: =OFFSET(WorkingPivot!$A$5,,,COUNTA(WorkingPivot!$A$5:$A$1000),COUNTA(WorkingPivot!$A$5:$Z$5)) A5 is the left most corner of my pivot table. I am not sure what I'm doing...

I'm glad to see a solution that doesn't require macro, but do i use this named range for every page/worksheet produced via the Show Report Filter pages? Sorry, i just wanted to clarify. Thanks, Hui, for the help.

I have a pivot table that expands and contracts with data. I am constantly changing print areas to accomodate all the data and to hide the report filters so it looks more like an actual report. Does anyone know how a macro that I could use that could help me: 1. Print the pivot table...

Hi, Hui, Thanks for your quick response. It looks like Chandoo's grammy bump chart may work the best for my purpose. I like your 3 black charts and I understand how to do that and the only reason I can't do that is because my charts are stacked columns so "highlighting" it with one color...

Thank you, Hui for reminding me of this because I can't seem to think how I'll apply until you pointed it out. I'll give it try but am wondering if there's another technique, just in case...Reason I'm wondering is because all most of my charts are single chart with 40 bars (1 for each hospital...

Hi, I'm working on a lot of charts that compare 40 hospital performance to each other. Of those 40 hospitals, there are about 10 regions that those hospitals can be grouped into. As I'll be presenting data to these regions separately, I'd like to highlight the relevant hospitals in each...

Hi Hui, The -1 works great for this scenario but sometimes I have to minus 3 or so and the number could vary for it to work and i guess I could count the number of 0s in a column and ask it to use that column but was wondering if there's something more elegant that i'm missing? Thanks for help.

Thank you, Larph! I find this incredibly simple and useful. Thank you for sharing it with everyone.

Hi all, I just started using using the match formula along with index formula and am enjoying how powerful it is. I am wondering if you could help me figure out if there's another function that would better suited for my need. I'm trying to figure out what percentile a value belongs in...

Hi Hui, For some reason, I didn't think I got any response to my question because I e-mailed John Walkenbach and he said he didn't put it the book because he, himself, couldn't get it to work. I guess he hadn't met THE Excel Ninja yet. Thank you so much for the example. It is so helpful!

In chapter 7 of his 'Excel Charts' book from 2003, he has a section on creating a dynamic chart where the series plotted depends on the location of the Active Cell in the worksheet. In the new edition of this book, 'Excel 2007 Charts', his Table of Contents is very similar, but this section of...

Hi razaas, Although I don't have Excel 2007, I remember now how to do it. It's all in the Defininig name. For instance, rather than sum(a1:a10), you can name the area quantity and put sum(quantity). Thanks for prompting the idea.