INDIRECT with OFFSET

Question

Hello,

I am using an indirect in a data validation > list.  Like this

=INDIRECT((D33) & "_list" )

Which is in the cell to the right of C33.

So it will take the cell to the left and add "_list" as a range to populate in the drop down.

One of my ranges used OFFSET.  Like this

OFFSET(AllInventory!$A$2,0,0,COUNTA(AllInventory!$A:$A)-2,1)

This allows auto expanding.  And also remove the top and bottom values out.

When using a regular range my setup works fine.  Such as

range = area_list

='AllInventory'!$A$33:$A$236

But the OFFSET does not work.

The dropdown doesn't have anything in it.  Like there is no list at all.

Has anyone seen this?

Does anyone know a workaround for this?

thanks

Answer 1

See http://www.contextures.com/xlDataVal15.html for a very general setup, or this post by Aladin Akyurek for a different approach.