Data Validation not seeing My Dynamic Range PLEASE HELP

[bamaisgreat]

I had the great idea of changing some of my named ranges to Dynamic Ranges.
I did not research this before doing so, now nothing shows up in my drop downs.
All I have in the Data Valadation Source Box is =INDIRECT (I28)
I have read that Dynamic and Indirect dont go together.
If someone could would you Please modify =INDIRECT (I28)
so it will work. This is very frustrating.
Thank you all

 

[Andrew Poulsom]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Create the names PLATE, BEAM, CHANNEL and MISC_CHANNEL that refer to D1, M1, O1 and P1 respectively. Create the names PLATECol, BEAMCol, CHANNELCol and MISC_CHANNELCol that refer to D:D, M:M, O:O and P:P respectively. Then in your validation try:

=OFFSET(INDIRECT(I28),1,0,COUNTA(INDIRECT(I28&"Col"))-1,1)

 

[bamaisgreat]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Andrew, I apoligize but Im not sure what you mean by respectively.
I have the

PLATE

BEAM

CHANNEL

MISC_CHANNEL all in one range. Are you saying also make a range with PLATECol, BEAMCol, CHANNELCol and MISC_CHANNELCol

<tbody>
</tbody><colgroup><col></colgroup>

 

[Andrew Poulsom]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Respectively means in the same order as written, ie PLATE refers to D1, BEAM refers to M1, CHANNEL refers to O1 and MISC_CHANNEL refers to P1.

 

[bamaisgreat]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Andrew thanks for the help. I also had the same question posted on another forum.
And recevied a link to http://www.excelforum.com/excel-tips...t-problem.html
which really explanes in depth what you were showing me.

 

[T. Valko]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Andrew thanks for the help. I also had the same question posted on another forum.
And recevied a link to http://www.excelforum.com/excel-tips...t-problem.html
which really explanes in depth what you were showing me.

I may have misunderstood what you were trying to do.

I didn't look at your file but read the additional replies to see where the thread was headed.

So, are you all squared away on this?

 

[bamaisgreat]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Valko yes Andrew gave me a link that had a example workbook in it and it looks like I can figure it out from it. Thanks.

 

[T. Valko]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Valko yes Andrew gave me a link that had a example workbook in it and it looks like I can figure it out from it. Thanks.

Good deal. Thanks for the feedback!

 

[bamaisgreat]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Andrew, I appreciate your help. The Contexture's Link with the Example Workbook really shows me what I need to do. The work around does not make much since but as long as it works thats all I need.
Thank you again

 

[bamaisgreat]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Andrew I thought I could figure this out with your Contextures link and another link I found in another forumn but Im getting crunched for time and I dont completly understand the formula.
If you are able could you look at My attached Workbook ? I have all the Ranges made and the valadation works in the first cell, I cant seem to get it to work in the second. Its something in the formula I think.
https://skydrive.live.com/redir?resid=BC39913B0562FDC9!869&authkey=!AHEEuY9Xxq0iijE
Thanks again

 

[Andrew Poulsom]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

You data validation formula should be:

=OFFSET(INDIRECT(SUBSTITUTE($I$28," ","")),0,0,COUNTA(INDIRECT(SUBSTITUTE($I$28," ","")&"Col")),1)