How to fix source limitation in data validation, i am looking for a solution

[Lakshmikar]

Im not able to use the below formula to keep in Source of data validation.

=IF(AND(E3="NETWORK",F3="FDC1"),NetFDC1,IF(AND(E3="NETWORK",F3="FDC2"),NetFDC2,IF(AND(E3="SECURITY",F3="FDC1"),SecFDC1,IF(AND(E3="SEURITY",F3="FDC2"),SecFDC2,IF(AND(E3="COMPUTE",F3="FDC1"),ComFDC1,IF(AND(E3="COMPUTE",F3="FDC2"),ComFDC2,
IF(AND(E3="BACKUP",F3="FDC1"),BckFDC1,IF(AND(E3="BACKUP",F3="FDC2"),BckFDC2,IF(AND(E3="STORAGE",F3="FDC1"),StrFDC1,IF(AND(E3="STORAGE",F3="FDC2"),StrFDC2))))))))))

How to fix it.



Requirement:

Attached is the example of data. In G3 i require drop down of "team 1 to Team6" when E3=NETWORK & F3=FDC1 and vice versa.

I tried for NETWORK,COMPUTE,SECURITY with formula above one but not able to add extra formula for STORAGE & BACKUP.
Looks some limitaton is there to fill the above formula in SOURCE.

[Glenn Kennedy]

Cab we rearrange J & K to get rid of those awful merged cells?

[Lakshmikar]

those are required for E3 and F3 to check and fill G3 with Drop down.

[Lakshmikar]

I mean J & K values are the information for me....im not using them in my formula.

[Glenn Kennedy]

I know that! But being merged cells which are pretty, they are horrible to get formulas to work with!! Ijust want to replace 1 merged cell containing Network, with 6 individual cells containing network.

[Glenn Kennedy]

No. But I want to, but not with those merged cells!!

[Lakshmikar]

Ok, please check updated one, removed merged cells.

[Glenn Kennedy]

I did not use your most recent attachment. This was done using the original sampole.

=INDEX(Sheet1!$L:$L,AGGREGATE(15,6,ROW(Sheet1!$J$2:$J$37)/((Sheet1!$J$2:$J$37=Sheet1!$E3)*(Sheet1!$K$2:$K$37=Sheet1!$F3)),1)):INDEX(Sheet1!$L:$L,AGGREGATE(14,6,ROW(Sheet1!$J$2:$J$37)/((Sheet1!$J$2:$J$37=Sheet1!$E3)*(Sheet1!$K$2:$K$37=Sheet1!$F3)),1))


I used this to create a Named Range (Track3) and set Track 3 as the Data Validation List.

[Lakshmikar]

Wow, Fantastic Glenn. In minutes you provided solution. I struggled for it for hours.
Could you explain steps how to do it. Next time similar work is there for me, will do it myself.

[Glenn Kennedy]

I have to go now. I promise I will reply in the UK morning. If I forget... remind me!!

[Lakshmikar]

Sure Glenn, have a good day...will remind you tomorrow.

[Glenn Kennedy]

Now that the merged cells are gone, you have a nice layout. The desired values for each combination are all beside each other., So what is needed, is a formula to return a range, starting at the first matching row and ending at the last matching row.

=INDEX(Sheet1!$L:$L,AGGREGATE(15,6,ROW(Sheet1!$J$2:$J$37)/((Sheet1!$J$2:$J$37=Sheet1!$E3)*(Sheet1!$K$2:$K$37=Sheet1!$F3)),1)):INDEX(Sheet1!$L:$L,AGGREGATE(14,6,ROW(Sheet1!$J$2:$J$37)/((Sheet1!$J$2:$J$37=Sheet1!$E3)*(Sheet1!$K$2:$K$37=Sheet1!$F3)),1))

Red = First matching row, blue - last matching row. How does each bit work? They differ only in the aggregate function: 15 (SMALL) and 14 (LARGE).

INDEX(Sheet1!$L:$L,AGGREGATE(15,6,ROW(Sheet1!$J$2:$J$37)/((Sheet1!$J$2:$J$37=Sheet1!$E3)*(Sheet1!$K$2:$K$37=Sheet1!$F3)),1))

Green: when these two criteria are met,
Cyan: return the row number
Red: smallest (or largest if 14 instead of 15)
Black: and return the corresponding value from column L.

So you then have the statrting point & ending point for the required range... Set up a Named Range and call the named Range in data validation. Job done.


You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Lakshmikar]

Thanks for the information. But not able to understand getting confused.

[Glenn Kennedy]

Well, if you want a further explanation - you'll have to be precise about where you are getting lost. You should also look at Formulas/Formula auditing/Evaluate formula