Dynamic Ranges

[tmmich71]

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

 

[R_Stephens]

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