Offset Dynamic Name Range with a twist

[iDeals]

I am trying to get a form control box to have a dynamic input based on a named range. I have named said range as "Selection" and want it to do a few things:

1. Offset from cell D1 to a column based on a match function
2. Have the Height of the Offset formula based on the column identified with the Match function.

I'm striking out on both fronts. Offset function I'm using:

=OFFSET(CodeList!$D$1,1,MATCH('LO - Initial Checklist '!Y5,CodeList!D1:N1,0),7,1)

Any help is appreciated

 

[Chrisdontm]

You could try this:

=COUNTA(OFFSET(CodeList!$D$1,1,MATCH('LO - Initial Checklist'!Y5,CodeList!D1:N1,0)-1,COUNTA(OFFSET(CodeList!$D$1,0,MATCH('LO - Initial Checklist'!Y5,CodeList!D1:N1,0)-1,1000,1))-1,1))

It uses an OFFSET formula to determine how tall the column is, so it can be used inside another OFFSET formula to define the range...

If I missed something you may have to adjust it a little bit...

 

[Johnny Thunder]

Tried the above and I am getting a weird error message - "The List Source must be a Delimited List, or a reference to a single row or column"

Any ideas how to modify?

 

[Chrisdontm]

I haven't messed with Form Control Boxes much, but in trying to make it work, it would not.

I am only guessing, but it might be due to me using Office 365.

I have seen some restrictions because of that before.

Are you using Office 365....?

Is there any way that you could use basic data validation instead...?

 

[iDeals]

I ended up taking a different approach and using INDIRECT in my named range, works like a charm.

Named Range: Selection

=INDIRECT("CodeList!"&'LO - Initial Checklist '!$Z$15&'LO - Initial Checklist '!$AA$15&":"&'LO - Initial Checklist '!$Z$15&'LO - Initial Checklist '!$AB$15)

To manually build out the address for the INDIRECT to work as I wanted took some additional steps:

Cell Z15

=SUBSTITUTE(ADDRESS(1,MATCH(Y5,CodeList!1:1,0),4),"1","")

Gets me my column letter

Cell AB15

=COUNTA(INDIRECT("CodeList!"&'LO - Initial Checklist '!Z15&":"&'LO - Initial Checklist '!Z15))

Gets the last row in that column so my range is dynamic