Hi Bruno,
Thanks a ton for helping me.
Thanks
Kalyan
On Tue, Aug 31, 2010 at 3:41 AM, Bruno Bruno <brunobbr...@gmail.com> wrote:
> Hi Maries,
>
> This formula might look complicated, but it's actually simple if we "break"
> it into pieces.
> But before doing it, we first need to understand the idea: we want to,
> based on the value of cell B7, return the corresponding range of possible
> values that are on another sheet.
> So, for example:
> *Aloe Vera* should return *'PRD Range'!A2:A3*
> *Gold* should return *'PRD Range'!D2:D6*
> And so on.
> *
> *
> So, our string is basically consisted of 5 elements:
>
> 1. The constant *'PRD Range'*
> 2. The column (ex: *D*)
> 3. The starting row and a separator (always *2:*)
> 4. Again, the column
> 5. The last row (ex: *6*)
>
> With that in mind, it should be easy to understand the execution. The *first
> step* of the string is the easiest:
>
> *=INDIRECT(**"'PRD Range'!"** **&** LEFT(ADDRESS(1,MATCH('Dropdown
> List'!$B$7,Brand,0),2),1+(MATCH('Dropdown List'!$B$7,Brand,0)>26))**&**
> "2:"**&**LEFT(ADDRESS(1,MATCH('Dropdown
> List'!$B$7,Brand,0),2),1+(MATCH('Dropdown
> List'!$B$7,Brand,0)>26))**&**COUNTA(INDIRECT("'PRD
> Range'!" & LEFT(ADDRESS(1,MATCH('Dropdown
> List'!$B$7,Brand,0),2),1+(MATCH('Dropdown
> List'!$B$7,Brand,0)>26))&":"&LEFT(ADDRESS(1,MATCH('Dropdown
> List'!$B$7,Brand,0),2),1+(MATCH('Dropdown List'!$B$7,Brand,0)>26)))))*
>
> The *2nd element* (which is equal to the *4th*, so it appears twice) is
> more elaborated:
>
> *=INDIRECT("'PRD Range'!" **&** **LEFT(ADDRESS(1,MATCH('Dropdown
> List'!$B$7,Brand,0),2),1+(MATCH('Dropdown List'!$B$7,Brand,0)>26))**&**
> "2:"**&****LEFT(ADDRESS(1,MATCH('Dropdown
> List'!$B$7,Brand,0),2),1+(MATCH('Dropdown
> List'!$B$7,Brand,0)>26))**&**COUNTA(INDIRECT("'PRD
> Range'!" & LEFT(ADDRESS(1,MATCH('Dropdown
> List'!$B$7,Brand,0),2),1+(MATCH('Dropdown
> List'!$B$7,Brand,0)>26))&":"&LEFT(ADDRESS(1,MATCH('Dropdown
> List'!$B$7,Brand,0),2),1+(MATCH('Dropdown List'!$B$7,Brand,0)>26)))))*
>
> Here I used the =ADDRESS(a,b,c) formula to return me the value I want. In
> the formula:
> *a = row of the reference *(for me this is irrelevant here, because I
> just want the column, so I put *1*)
> *b = column of the reference* (this is important, so I used the MATCH
> function to look in which column of the BRAND array the reference {ex: Gold}
> is {ex: 4})
> *c = the format of the return *(if this is omitted, it will return
> for example $A$1. Since I'm using the LEFT function to extract the column, I
> use the argument *2*, so the return will be in the format A$1)
> *LEFT(ADDRESS(1,MATCH('Dropdown List'!$B$7,Brand,0),2),1+(MATCH('Dropdown
> List'!$B$7,Brand,0)>26))*
>
> The last piece of code to the right is just a helper if eventually the
> column number goes beyond Z, the formula should get 2 letters to the left
> (AA, AB, etc..). Here I borrowed the idea of a book I can't remember, so I'm
> (kind of) giving it the credits.
>
> The *3rd piece of the string is also very simple:*
> *=INDIRECT("'PRD Range'!" & LEFT(ADDRESS(1,MATCH('Dropdown
> List'!$B$7,Brand,0),2),1+(MATCH('Dropdown
> List'!$B$7,Brand,0)>26))&"2:"&LEFT(ADDRESS(1,MATCH('Dropdown
> List'!$B$7,Brand,0),2),1+(MATCH('Dropdown
> List'!$B$7,Brand,0)>26))&COUNTA(INDIRECT("'PRD
> Range'!" & LEFT(ADDRESS(1,MATCH('Dropdown
> List'!$B$7,Brand,0),2),1+(MATCH('Dropdown
> List'!$B$7,Brand,0)>26))&":"&LEFT(ADDRESS(1,MATCH('Dropdown
> List'!$B$7,Brand,0),2),1+(MATCH('Dropdown List'!$B$7,Brand,0)>26)))))*
>
> And finally, the *5th piece might look the most complicated, but is also
> simple:*
> *=INDIRECT("'PRD Range'!" & LEFT(ADDRESS(1,MATCH('Dropdown
> List'!$B$7,Brand,0),2),1+(MATCH('Dropdown
> List'!$B$7,Brand,0)>26))&"2:"&LEFT(ADDRESS(1,MATCH('Dropdown
> List'!$B$7,Brand,0),2),1+(MATCH('Dropdown
> List'!$B$7,Brand,0)>26))&COUNTA(INDIRECT("'PRD
> Range'!" & LEFT(ADDRESS(1,MATCH('Dropdown
> List'!$B$7,Brand,0),2),1+(MATCH('Dropdown
> List'!$B$7,Brand,0)>26))&":"&LEFT(ADDRESS(1,MATCH('Dropdown
> List'!$B$7,Brand,0),2),1+(MATCH('Dropdown List'!$B$7,Brand,0)>26)))))*
>
> Here, the idea is to count the elements in the column so we get to know the
> row of the last one. See how the formula gets much simpler when we
> substitute the piece of code we used in steps 2 and 4 for the word *COLUMN
> *:
> *COUNTA(INDIRECT("'PRD Range'!" & COLUMN &":"& COLUMN))*
> So, for example, if our column is *D*, we get:
> *COUNTA(INDIRECT("'PRD Range'!" & D &":"& D))*
> Which is reduced then to:
> *COUNTA(INDIRECT("'PRD Range'!D:D"))*
>
> So, counting the non-blank cells in range D, we got to know our 5th element
> (in the example, *6*).
> After joining this 5 informations together, our formula is reduced to:
> *=INDIRECT(**"'PRD Range'!D2:D6")*
> What is precisely the range we want.
>
> Even if there's a more elegant way of solving it, I think this is a
> very didactic example of thinking in the excel way.
>
> Regards,
> Bruno
>
>
> On Mon, Aug 30, 2010 at 11:46 AM, Maries <talk2mar...@gmail.com> wrote:
>
>> Hi! Bruno,
>>
>> Can you explain me the logic which you used second data validation
>>
>> =INDIRECT("'PRD Range'!" & LEFT(ADDRESS(1,MATCH('Dropdown
>> List'!$B$7,Brand,0),2),1+(MATCH('Dropdown
>> List'!$B$7,Brand,0)>26))&"2:"&LEFT(ADDRESS(1,MATCH('Dropdown
>> List'!$B$7,Brand,0),2),1+(MATCH('Dropdown
>> List'!$B$7,Brand,0)>26))&COUNTA(INDIRECT("'PRD Range'!" &
>> LEFT(ADDRESS(1,MATCH('Dropdown List'!$B$7,Brand,0),2),1+(MATCH('Dropdown
>> List'!$B$7,Brand,0)>26))&":"&LEFT(ADDRESS(1,MATCH('Dropdown
>> List'!$B$7,Brand,0),2),1+(MATCH('Dropdown List'!$B$7,Brand,0)>26)))))
>>
>> Regards,
>>
>> Maries
>>
>>
>> On Mon, Aug 30, 2010 at 5:58 PM, Bruno Bruno <brunobbr...@gmail.com>wrote:
>>
>>> Hi Kalyan,
>>> See if this helps.
>>>
>>> Regards,
>>> Bruno
>>>
>>>
>>> On Mon, Aug 30, 2010 at 6:04 AM, Kal xcel <kalx...@gmail.com> wrote:
>>>
>>>> Dear Experts,
>>>>
>>>> I need a help to create multiple linked dropdown lists. Please go
>>>> through the attached file, details is there.
>>>>
>>>> Thanks in advance
>>>>
>>>> Kalyan
>>>>
>>>> --
>>>>
>>>> ----------------------------------------------------------------------------------
>>>> Some important links for excel users:
>>>> 1. Follow us on TWITTER for tips tricks and links :
>>>> http://twitter.com/exceldailytip
>>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>>
>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>
>>>> <><><><><><><><><><><><><><><><><><><><><><>
>>>> HELP US GROW !!
>>>>
>>>> We reach over 7000 subscribers worldwide and receive many nice notes
>>>> about the learning and support from the group.Let friends and co-workers
>>>> know they can subscribe to group at
>>>> http://groups.google.com/group/excel-macros/subscribe
>>>>
>>>
>>> --
>>>
>>> ----------------------------------------------------------------------------------
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> HELP US GROW !!
>>>
>>> We reach over 7000 subscribers worldwide and receive many nice notes
>>> about the learning and support from the group.Let friends and co-workers
>>> know they can subscribe to group at
>>> http://groups.google.com/group/excel-macros/subscribe
>>>
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> HELP US GROW !!
>>
>> We reach over 7000 subscribers worldwide and receive many nice notes about
>> the learning and support from the group.Let friends and co-workers know they
>> can subscribe to group at
>> http://groups.google.com/group/excel-macros/subscribe
>>
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 7000 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
