Dynamic Ranges

tmmich71

New Member
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

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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

Replies
4
Views
485
Replies
7
Views
360
Replies
6
Views
690
Replies
0
Views
650
Replies
1
Views
647

1,219,027
Messages
6,145,857
Members
450,652
Latest member
JR1

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.

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