Using LEFT with OFFSET to get a dynamic drop down list

**alcopoppa** · 01-19-2012, 01:07 PM

Hello,

My first post, so thank in advance for any help!

I am trying to use data validation and dynamic drop downs using the following formula. This returns the cell values I want (a list of names dependent upon their class). This currently works, however I want to amend it to return only the first 2 letters at the left of the cel. The current data validation forumla is:

=OFFSET(regstart,MATCH(C2,regcolumn,0)-1,1,COUNTIF(regcolumn,C2),1)

I thought that this would work, however it does not:

=LEFT(OFFSET(regstart,MATCH(C2,regcolumn,0)-1,1,COUNTIF(regcolumn,C2),1),2)

Any ideas?

Thanks very much.

**alcopoppa** · 01-20-2012, 05:02 AM

Hi,

Any ideas anyone?

Thanks.

**Kyle123** · 01-20-2012, 05:25 AM

I don't think you can, at least not directly - I think that Left only accepts a single string rather than a range

Can you not set up a dynamic range with just the left two characters and reference that?

**alcopoppa** · 01-20-2012, 05:56 AM

Hi,

Thanks for the reply - yes I didn't think of that, it would have been far easier!

Thanks again.

**Marcol** · 01-20-2012, 08:24 AM

@ Kyle

Can you not set up a dynamic range with just the left two characters and reference that?

See this workbook uses INDEX() to show what the range LEFT($A$2:INDEX($A:$A,MATCH(REPT("z",255),$A:$A,1),1),2) contains.

Use Tools > Formula Auditing > Evaluate Formula to see the range contents.