Referencing a specific Column in a named range

[StephenMcGill]

Probably a newbie question, but I have the formula below that finds rows wherever column Q is blank and sends back the row number

{=IF(ISERROR(SMALL(IF('Production Meet. Mins '!$Q$13:$Q$99="",ROW('Production Meet. Mins '!$Q$13:$Q$99)),ROW(1:1))),"",SMALL(IF('Production Meet. Mins '!$Q$13:$Q$99="",ROW('Production Meet. Mins '!$Q$13:$Q$99)),ROW(1:1))) }


Instead of explicitly referencing 'Production Meet.Mins!$Q$13:$Q$93' for this formula, I want to reference a named range PRODWIP that I have set up as $B$13 to $AD$93 so I need to reference Col 16 in the named range - but I have no idea how to do that. i'm guessing "Index something" but I cant get my head around it despite hours on online research.


Similarly, I need to reference the Named Range here...
=IF(ISERROR(INDEX('Production Meet. Mins '!$B$13:$Q$99,SMALL(IF('Production Meet. Mins '!$Q$13:$Q$99="",ROW('Production Meet. Mins '!$Q$13:$Q$99)),ROW(1:1))-12,1)),"",INDEX('Production Meet. Mins '!$B$13:$Q$99,SMALL(IF('Production Meet. Mins '!$Q$13:$Q$99="",ROW('Production Meet. Mins '!$Q$13:$Q$99)),ROW(1:1))-12,1))



hope you can help point me in the right direction

thanks
Stephen

 

[Snakehips]

Stephen,

Eg to reference column 16 of PRODWIP use...

INDEX(PRODWIP,,16)

If you enter =INDEX(PRODWIP,,16) in a spare cell then select the cell, highlight all bar the equals sign in the formula bar and then key F9 the formula bar will show the evaluated array that represents values of column 16.

Hope that helps.

 

[StephenMcGill]

Stephen,

Eg to reference column 16 of PRODWIP use...

INDEX(PRODWIP,,16)

If you enter =INDEX(PRODWIP,,16) in a spare cell then select the cell, highlight all bar the equals sign in the formula bar and then key F9 the formula bar will show the evaluated array that represents values of column 16.

Hope that helps.

AWESOME ! thanks for that Tony - it works =SMALL(IF(INDEX(ProdWIP,,18)="",ROW(INDEX(ProdWIP,,18))),ROW(1:1))