Copy cell range from one cell into another (subtotals)

Solola

Board Regular
Joined
Sep 23, 2003
Messages
73
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?
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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

Board Regular
Joined
Sep 23, 2003
Messages
73
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top