Get values from validation list; else type in values?

[Ed from AZ]

On Sheet1 I have a three-column table: ItemName, Value1, Value 2. I
created a dynamic named range for ItemName so I can use these names in
a drop-down list in Sheet2 via Data Validation. That way, I can add
new items to the list and they will show up in the drop-down list.

On Sheet2 I have a "calculator" that will need the values from the
table on Sheet1. So when I use the drop-down in Col A to select an
ItemName, Value1 for that item appears in Col B and Value2 appears in
Col C. I also need to allow the user to type in an item not on the
list and type in the values associated with that item, but without
disturbing any formulas.

This seems pretty basic - maybe it's just too early in the morning!
Can someone please drop-kick me in the right direction?

Ed

 

[T. Valko]

This seems pretty basic

Actually, that's not basic at all and would be rather complex requiring
sophisticated VBA programming. However, there is a simple solution to this.
Use a separate set of cells for when the user needs to manually enter these
items.

 

[Ed from AZ]

Well, at least I pick the _tough_ ones to get stumped on!! (This
time, anyway!)

But it gets worse . . . I'm trying to reference the dynamic range
ItemName_Here by a data validation list based off choosing Here is
another validation list. I've tried all sorts of methods, including
INDIRECT and CONCATENATE, to get validation to recognize "ItemName_" &
selected Here as a valid range reference, but it won't work.

I was trying to do all this without VBA, but it looks like I may have
no choice.

Ed

 

[T. Valko]

But it gets worse . . .

Of course! It always does, doesn't it? <g>

Dynamic ranges defined using functions like OFFSET won't work when "called"
using INDIRECT. There is a possible workaround using CHOOSE but it's limited
depending on what version of Excel you're using.

--
Biff
Microsoft Excel MVP


Well, at least I pick the _tough_ ones to get stumped on!! (This
time, anyway!)

But it gets worse . . . I'm trying to reference the dynamic range
ItemName_Here by a data validation list based off choosing Here is
another validation list. I've tried all sorts of methods, including
INDIRECT and CONCATENATE, to get validation to recognize "ItemName_" &
selected Here as a valid range reference, but it won't work.

I was trying to do all this without VBA, but it looks like I may have
no choice.

Ed

 

[Ed from AZ]

There is a possible workaround using CHOOSE but it's limited

depending on what version of Excel you're using.

Sorry - I've got XL 2007.

I looked at CHOOSE in Help. I tried =CHOOSE(1,ItemName_Here), but got
the #VALUE error.

Ed

 

[T. Valko]

You would use CHOOSE in conjunction with other functions. Basically, you use
it to pass the range (defined name) to other functions in the formula where
INDIRECT would cause a #REF! error.

--
Biff
Microsoft Excel MVP

There is a possible workaround using CHOOSE but it's limited
depending on what version of Excel you're using.

Sorry - I've got XL 2007.

I looked at CHOOSE in Help. I tried =CHOOSE(1,ItemName_Here), but got
the #VALUE error.

Ed

 

[Ed from AZ]

I'll have to play with that one a bit.

Thanks for all the help.
Ed