How to use Offset Formula for specific validation

[MaunishP]

Hi Team,
I have 2 worksheet sheet, TrackTime & Listing. In TrackTime if any team member selects any listing from Column E, he should only get option which are categories in Worksheet Listing in from Column E & F. How to use Offset formula.

I have attached sheet.

Regards,
Maunish Patel

 

[Hui]

If you want a single column of Listing Column E & F
Select the cells in Tracktime Column E
Goto Data Data Validation
Allow: List
Source: =Listing!$E$2:$E$50 or =Listing!$F$2:$F$50

If you want a Two columns Listing Column E & F
You will need to make up a dummy Range first
Goto Listing G2
Enter: =E2&" - "&F2
Copy down

Now Select the cells in Tracktime Column E
Goto Data Data Validation
Allow: List
Source: =Listing!$G$2:$G$50

 

[MaunishP]

Hi Hui,
Attached image will help you describe more easier and also have attached updated file.

1) Select specific work activity from drop down. Based on work activity down, only aligned activity in Listing Column D should be reflected.

2) After selecting proper work activity, neccessary processs should reflect from Listing Column G.

I tried using formula however after few rows, offset range is getting shifted which i dont want to get them shift.

 

[MaunishP]

Hi Hiu,
Did you got a chance to review request?

 

[Hui]

No
My apologies
I've been pretty busy, sleeping
Please read the Site Rules
Especially the one about the world operating 24 hrs a day

 

[Nebu]

Hi:

Follow these links
http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/
http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/
http://chandoo.org/wp/2010/09/13/dynamic-data-validation-excel/

Thanks