Data validated [SOLVED]

[ananthram]

Hi All,


i need a help on file which is in below given link.


https://app.box.com/s/8muubfilwvmz0fmfx383


When i select data validated value in role column which is in Sheet1 it should only the role related task where the table in SRC sheet. This should be a formula or a macro.

 

[Smallman]

Hi Ananthram


My first port of call would be to create a formula that captured the Unique Values in list then use a dynamic range to refer to those values. I found that it was difficult to generate a dynamic range that just referred to the visible cells only.


Hey those blank cells you have from D28 onwards are priceless. With a bit of discipline could they be eradicated? Adds an extra line of code to my procedure : )


So I jumped into vb. I made a dynamic named Range in XL called Listo and ran this code on entry into your Sheet4.

[pre]

Private Sub Worksheet_Activate()
[D1:d2000].AdvancedFilter 2, [L1], , True
[L2:L1000].Sort [L2], 2
End Sub

[/pre]

My coding runs like lightning and you don't notice it in the background.


I will post a workbook to prove workings shortly. Done>>>>>

http://rapidshare.com/files/296625329/Track%20sheet2.xlsm


But I would be very interested in the boffins opinion on getting a dynamic named range that only grabs visible cells. Formulas with Null values tend to get caught up in Dynamic ranges. Is this even possible????? Mr Weir perhaps a challenge if no one steps up to the mound?


Good night all.


Smallman

 

[srinidhi]

Without opening the workbook, as it does not open in my office. You can try this.


Dynamic Drop Down/ Drop Down based on other cell value


Highlight the data>Formulas Tab>create from selection.This will create 2 named ranges based on your selection. The first named range will be of your heading & the 2nd will be the data apart from the heading.


Insert this in the list in data validation.


OFFSET(Heading of the result colum ,MATCH(the first dropdown,Range for 1st dropdown data,0),0,COUNTIF(Range for 1st dropdown data,the first dropdown),1)

 

[srinidhi]

If there are blank cells in the data. highlight the data press f5 > go to special box appears, choose blanks, press ok. the blank cells are highlighted home tab>Delete>Delete Cells & select the shift cells left. This will not show the blank cells in the drop down list

 

[ananthram]

sorry but i need those task in data validation.


`example: if i select Content reviewer in role it should show only the content reviewer's task in task column with data validation


P.S: just like forms which will be in some internet registration page.

 

[Smallman]

Ananthram


So if you choose Quality control then you only want to see


QC_1

QC_2

QC_3

QC_4

QC_5

QC_6

QC_7

QC_8


in the task sheet? Should have had the same header on both sheets (Task on Sht1 and Task on Sht4. Instead of the creative writing "Activity".


You are looking for a cascading dynamic list. Have some examples but I will get to it tomorrow if no one jumps in in the mean time. IT is a pretty well worn topic and a simple GOOGLE search on CASCADING Validation LISTS will see you in the right place.


Take it easy


Smallman

 

[ananthram]

Ty sir,


as u said exactly wat i am expecting too i need like u gave below

[pre]

So if you choose Quality control then you only want to see

QC_1
QC_2
QC_3
QC_4
QC_5
QC_6
QC_7
QC_8

in the task sheet?

[/pre]
>> Yes, i need like this

 

[bobhc]

Good day Ananthram


Is this what you are after


https://dl.dropboxusercontent.com/u/75495784/Ananthram_Track_sheet.xlsx

 

[ananthram]

Yuppie!!~


Bobhc Sir, u made my day this is exactly wat i needed... thanks a lot.

 

[bobhc]

Good day Ananthram


Pleased it is the answer you wanted, I hid the DataValidation sheet knowing you would find it but I would keep it hidden from users. The Data Validation and its corresponding dependent validation fill every cell in those two columns so you have a lot of work to do to fill them all in


Even more pleased you took the time and trouble to thank the forum, not enough do that


You could copy the formula in the Billable column down when you have entered your data, as there is always some thing in the Role column if you auto copied down the answer in billable would read Yes as there is always something there, the data validation.


ps, did not use cascading lists, did not want to get my head wet