Nested Data Validation

[smittal]

Hello All,

i am in dilemma for this thread from around more than an year. i already read http://chandoo.org/forum/threads/re...mation-from-a-different-cell.4529/#post-23667 link on chandoo.org website but sorry, my Requirement is little different.

suppose, in an org there are 5 PM's and each PM handling 10 APM's so, in total we have 50 APMs with us. Cell A1 contains list of PMs and whenever we select PM name in A1 then only respective PM reportee list should be visible in B1 - in the form of "Data Validation".
e.g.

PM1 - APM1.. APM10
PM2 - APM11.. APM20...and so on.

How should we do the same. please suggest.

Thanks in advance for any help from anyone.

 

[NARAYANK991]

Hi Shweta ,

Can you download this file and see ? I made this before I saw that you have uploaded your data file.

https://www.dropbox.com/s/j6b1iwjg50wp0nl/Example_Shweta.xlsx

Narayan

 

[smittal]

Thank you so much Narayank991 sir. This is the thing i was searching from last 1 year.

 

[smittal]

Hi All,

Need little more assistance in same. i have attached a file in which 4 more tabs are added (sheet with PM name). What i am trying, whenever we select any PM and APM name in Main tab then this will select the data from Respective PM tab automatically display the Month Wise score in main tab.

 

[Luke M]

How's this look?

 

[Haseeb A]

Hello Smittal,

Another way is VLOOKUP.

=IFERROR(VLOOKUP($G2,INDIRECT("'"&$F2&"'!B:IV"),MATCH(D5,INDIRECT("'"&$F2&"'!B4:IV4"),0),0),"N/A")

Also,

for dependent data validation, my personnel recommendation is, keep them in a separate sheet in separate columns. So this way

1. Sub list can sort easily
2. Add/Delete list easily
3. Easy to maintain.

I have added an extra tab in your sheet named, 'DV Sheet' check that. Used 5 named ranges to get dependent validation for easy understanding.

 

[smittal]

Thanks both (Luke & Haseeb).. i am not very expert in Excel and Access (both). I read both the formula but need to put extra effort to understand the formula of Haseeb. Both formula's are working fine. Thank you everybody for the same.

 

[smittal]

Again Stuck, . There is a little change in PM1 tab. Let suppose, if every month is split into 2 parts, (First 15 Days and last 15 days) and in main sheet we want to fetch the value of last 15 days data only, then what should be the modified formula for same. please help.

 

[Brij Arora]

hi Smittal,
desired outcome achieved by little tweaking of formulae
=IFERROR(INDEX(INDIRECT($F$2&"!1:1000"),MATCH($G$2,INDIRECT($F$2&"!B:B"),0),MATCH(F$5,INDIRECT($F$2&"!4:4"),0)+1),"")

file attached for ready reference .

Brij A

 

[smittal]

Thanks Brij, Got the answer.