# Copy cell range from one cell into another (subtotals)

#### Solola

##### Board Regular
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?

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Greg Truby

##### MrExcel MVP
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
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
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).

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,834
Messages
5,855,899
Members
431,772
Latest member
dannyboi1

### 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.

### Which adblocker are you using?

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

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