Search results

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.

Thanks, Hui for chiming in to help. My problem isn't so much the calculation, it's the fact that the Age column could move and might change reference from H2:H200 to another K2:K200. I just want to make sure i reference the column heading if possible because new columns are always added. i...

I am trying to calculate the average age of a group of patients under the column heading of Age. Is there anyway to calculate it as to point out the column heading title instead of the referencing the column range reference. Also, the age range can vary as well (meaning, sometimes, there's 30...

Hi Hui, sorry I wasn't clearer in my question. I simply mean for the cursor to move upon pressing "Enter" to the appropriate cells depending on what was entered previously in cell A1. I hope that makes sense. Thank you for your help. Twee

Hi all, I am trying to design a 10 page form in excel and am hobbling through it because I know Access is a better way to go about doing this...Anyway, I'm using Excel due to accessibility to MS Access, but I digress. To make the form flow better, is there a good way to make it skip to next...