Excel Named Range / Multiple Data Validation issue

[gonikakos]

Hello dear Excel experts.

I am working on an Excel file that will be used for time management.
I have 3 columns like the ones below (each column is a named range) in the Control worksheet:

[TABLE="width: 300"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Actions[/TD]
[TD]Project[/TD]
[TD]Proposal[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Project[/TD]
[TD]Project1[/TD]
[TD]Proposal1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Proposal[/TD]
[TD]Project2[/TD]
[TD]Proposal2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Training[/TD]
[TD]Project3[/TD]
[TD]Proposal3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Meeting[/TD]
[TD]Project4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Trip[/TD]
[TD]Project5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Vacation[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In another Sheet there is 2 cells for each day in order to report what the employ did in each day:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]Α[/TD]
[TD="align: center"]Β[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1/1/2016[/TD]
[TD][Actions][/TD]
[TD][Description][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/2/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/3/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

etc...

The [Actions] cell is a Data Validation taken from the first table. (Project/Proposal/Training etc.)

The [Description] C1 cell has a data validation of

=indirect(B1)

.

The Name for "Project" is the following:

=OFFSET(Control!$E$2;0;0;COUNTA(Control!$E:$E)-1;1)

A similar function exists for the Name "Proposal".

=OFFSET(Control!$F$2;0;0;COUNTA(Control!$F:$F)-1;1)

As you can see I've made the range dynamic, because new projects and new proposals are added all the time and not include the blank cells.

However while the offset function is correct if I use it outside the name range, it doesn't work in the Data Validation.
As soon as I select the Action (for example: Project), the cell in C Column is not populated.

Can you please help? It's driving me nuts! I know the formula is correct, but i do not know why it's not working inside the Named Range/Data validation.

Thank you for your time.

 

[gonikakos]

so... noone? please i'm pretty desperate about this.

 

[ww4612]

your DV formula is limited.
indirect is not a good formula. in your formula =indirect(B1), the value of B1 must be an absolute address, not a relative address. and it must be the address in the same sheet. i suggest you to use another formula for your DV like index

 

[XOR LX]

Hi.

See my recent solution here:

http://www.mrexcel.com/forum/excel-...-down-list-name-manager-cell-named-array.html

The combination of INDIRECT and dynamically-defined ranges is not permitted with regard to Data Validation. Instead, you can employ EVALUATE, as I point out in that post.

Regards

 

[gonikakos]

XOR Thank you very very much!

I work with Excel, many many years now, but I've never heard of the Evaluate function!

Once again, thank you. You are a life saver.

I'm guessing this is done because of the issue/limitation that ww4612 mentioned regarding the indirect function. Dear ww4612, thank you for this information also!

 

[XOR LX]

You're welcome.

Actually I'm not quite sure to what ww4612 was referring, though it is not relevant here.

To clarify: you can use INDIRECT in such cases, though only if the ranges being passed to the Data Validation are not being generated dynamically. If they are, then you need to switch to a set-up using EVALUATE, as I give. Alternatively, you can abandon the dynamic idea altogether and instead use static, fixed ranges (in which case you can use INDIRECT).

Regards