Dynamic Ranges

tmmich71

New Member
Joined
Sep 7, 2006
Messages
3
I'm not sure if this is possible, so any help would be great. :)
I'm trying to figure out if there is a way to create 2 separate dynamic ranges from 1 table based on the information in one of the columns. The information in this table changes each time it is used. I need the dynamic ranges to be based on the Y/N column. If the number of rows that are "Y" changes, the range will adjust and the same for the "N". So, I have a dynamic range with the "Y" data and a dynamic range with the "N" data. I was using the offset function to include the entire table.

Here is a sample of my table:

Y/N MV_NM visits payments
Y TRANSITIONAL CARE UNIT 2 8595
Y HOSP-PSYCH UNIT 6 17947.6
Y MEMORIAL HOSPITAL 154 556618.42
Y TRANSITIONAL CARE SOUTH 1 3432
N HOSPITAL 7 76772.44
N MEMORIAL HOSPITAL 2 2905.16
N CARE UNIT PROGRAM 1 4234
N HOSPITAL 0 0
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

R_Stephens

Board Regular
Joined
Dec 23, 2004
Messages
143
Hi tmmich71,

The way I would do this (assuming your Y/N data starts in cell A2) would be to set up a named range for however much data you expect in the sheet at one time. So if you expect there never to be more than 50 rows of data use the following


Name: ynrange
Refers to: =Sheet1!$A$2:$A$51
(for more than 50 rows of data increase the range here)


From your information I am also assuming all your Y's are first in the list then all your N's (if not this won't work!)

Name: Y
Refers to: =OFFSET(Sheet1!$A$2,0,0,ROWS(ynrange)-COUNTIF(ynrange,"N")-COUNTIF(ynrange,""),4)

Name: N
Refers to: =OFFSET(Sheet1!$A$2,COUNTIF(ynrange,"Y"),0,ROWS(ynrange)-COUNTIF(ynrange,"Y")-COUNTIF(ynrange,""),4)

Hope it helps

Richard
 

Watch MrExcel Video

Forum statistics

Threads
1,130,045
Messages
5,639,744
Members
417,108
Latest member
Thein Than

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