[mybecca]

Hello,
I'm trying to build an interactive report which allows users to select specific values from a list or combo box populated by an array and then I'd like my macro to read that value and perform some calculations using the countifs and medianif commands and graph results.

Here is an example of my countifs formula that I would like to read "Surgeon A" and "Procedure Name" from user input:

'=COUNTIFS($A$2:$A$1243,"Surgeon A",$C$2:$C$1243,"Procedure Name")

The array containg this data will change with time.

For my Median if command, I'd like it to also read the Surgeon and the procedure and then calculate the median without me having predefined a range. Here is what I have now and it works fine, if I define a specific range

'=MEDIAN(IF(A2:A154 ="Surgeon A",D2154," "))

How would I modify this to look for both Surgeon and Procedure and calculate median?

And finally, not every surgeon will have performed every procedure. What is the best way to ignore or eliminate the null or #value results before applying the graphic macro?

Thank you so much for your help.

[Zack Barresse]

Hi there, and welcome to the board!

How can you do this without having a pre-defined range? Are you wanting the user to select it? Can you give us a little more information about the scope and purpose of this workbook? And why do you need a macro? Why can't you just use formulas and have your users choose data from cells, perhaps with in-cell drop down selections? Can you provide us with a workbook?

[mybecca]

Hello Zach and thank you for your help.

I'm trying to design a report with a main menu of sorts with different command buttons that will show various slices of the underlying data, which could be total cost, median cost, count by surgeon, by procedure or both - most of which will be displayed graphically.

Every month, I will receive additional data and would like to build functionality that allows a user to select a Surgeon, or select a procedure and see results without having to select or define a specific range. I also have a more comprehensive spreadsheet which contains the surgical supply data that underlies the costs that I will likely do the same type of work with (e.g., select a supply category (PORTS))

I have written a variety of countifs, median(if) formulas that I will incorporate into the macro that will calculate the various values, hopefully based on the user input.

It's been several years since I've written macros and I've been reading and researching and I believe I know how it want it to work, it is incorprating all these ideas into code that has me a bit intimidated.

I hope this makes more sense.

[Zack Barresse]

What version of Excel are you using? Why not use a Pivot Table? If using 2010, you could really make a report look good with Slicers, creating a very easy user dashboard. If you have some calculated fields, you can show whatever you want. Where exactly is the data stored? Give some scope on what you're looking for a solution. Are you wanting to create a file for multiple people to access? Will this be a tool to install on others computers? Details please.

[mybecca]

Hello,
I'm using Excel 2007

I've thought about using pivot tables to summarize some of the raw data, but I'm tasked with creating charts and tables that allow the user to select different variables.

I have figured out all my count and median calculations and have macros that cut and paste out the sets of data for each graphic or table.

I was hoping to add in drop down boxes that would allow a user to select a certain procedure, surgeon, supply type which are read by the macro and the various outputs created.

It is possible I'm trying to be fancier than necessary as I have already generated the basic graphics based on cost variability by surgeon/procedure. I was just hoping to give the options to drill down based on the users various questions, without having to pre-think and build every possible scenario.

I apologize if I'm being to vague about what I'm trying to accomplish. I just hope to find out if what I dream of doing is even possible with VBA.

Thanks for your ongoing feedback and assistance.

[Zack Barresse]

From a Pivot Table you can create a Pivot Chart as well. This enabled charting, plus the filtering effect of pivoted data, plus drill down on the Pivot Table. Sounds like exactly what you need. ?? You can manipulate or create Pivot Tables via VBA as well if you wish, but I think you should be able to create your Pivot Table one time though.