Copy cell range from one cell into another (subtotals)

[Solola]

I use the subtotals function profusely. However, I want to use functions that are not available in the subtotals function. For example, I want to generate a median for a range of numbers that are subtotaled by Center Number, but the median function is not available when I subtotal.

What I have been doing is generating any random subtotal to get my breakout by Center: At Each Change in Center Number, Use Function Count, Add subtotal to Center Number. Then, once my subtotals have been created, I manually add the median function on EACH newly created subtotal line. This gets very cumbersome, b/c I have over 100 Center Numbers, so I have to add the median function that over 100 times. I've tried running a macro to do it for me, but I can't get it to pull the correct range, since each subtotal is totalling a different number of rows (I'm not that adept at macros).

Macros aside, what I REALLY want to do is figure out a way to use the Go To function, select visible cells, and paste a formula into each subtotal line. I have no problem doing this in and of itself (if it were a simple formula), but I DO have difficulty pulling the correct range into each subtotal line for the median. How do I copy ONLY the range used in the count function into my median function? I've tried telling Excel to =find() the comma in the subtotal so I can use it in a =mid() formula and return the range, but it's looking for the comma in the value, not the formula, so that doesn't work.

So, my goal is to pull the range from one formula (only a portion of the original formula) into another formula and Control-Enter (copy) that into all selected cells (using each line's own range, rather than copying the range from the first cell into all of them).

Any ideas?

 

[Greg Truby]

Can you not just highlight the column in question and do a Search and Replace, Find What being =SubTotal(9, (or =SubTotal(2, if you used the Count option) and Replace with being =Median(?

 

[Solola]

Yes, that WOULD work, but sometimes I want to keep my original subtotal. I.e., I'd want count AND median. But, I suppose I could just throw a 2nd random subtotal in there, and find and replace that one, couldn't I?

THANK YOU!! (Sometimes the simplest solutions are the hardest to come up with without a fresh set of eyes...)

 

[Greg Truby]

Yep. Then, let's say your data is in B and you want the Count subtotals in B and the Medians in C. Add your surplus Subtotal in C, do the Find and Replace to make the formula =Median(). Then I'd switch to R1C1 View (Tools | Options... | General (tab) | R1C1 Ref Style (checkbox)) then do two more Search and Replaces: searching for C: replacing with C[-1]: and then searching for C) replacing with C[-1]). Then flip back to A1 view. Make sure you highlight just column C for this second round of search and replace operations - otherwise you'll impact your Subtotal formulas in column B as well (yeah, voice of experience).