Conditional data entry WITHOUT using a Macro

[vilas desai]

Dear Experts,
Attached two files, 1. Datasheet with ws name as "Datasheet" 2. LT_Comparisons
LT_Comparisons is to show how i would like to have the output as.
Cell T4 in Datasheet has option lists, and this is what I want to achieve based on selection in T4.
I DONT WANT TO DO THIS WITH A MACRO. Also the formula should not be seen and edited by the user.

Example:

If T4 = Capacitance, then
C6 = abc, E6=def, E7=ghi etc
and
C=20 = JKL, E20 =MNO, E21=pqr, etc

ELSE,
If T4 = Nuclear, then
C6 = aaa, E20=bbb, E21=ccc etc
and
C=20 = JKL, E20 =MNO, etc

ELSE,
If T4 = Ultrasonic, then
C6 = aaa, E20=bbb, E21=ccc etc
and
C=20 = ddd, E20 =eee, etc

till all options are over.

Please help
Best regards
Vilas Desai

[HansV]

The drop-down list in T4 has 6 options, but the sheet in LT_Comparisons has only 5 options: there is no section for Resistance.

[vilas desai]

I am sorry Sir. Here it is
Best regards
Vilas Desai

[HansV]

See the attached version. You may have to change the source of the links.

Datasheet.xlsx

[vilas desai]

Thanks Hans. I actually anted to use If / Then / Else IF kind of statements. Is that something which I am optimistic about?

[HansV]

Why do you prefer that?

[vilas desai]

Sir That is a very good question. This WB is being downloaded from a cloud based application. The user has no provision to access links. The Cloud app also has no provision to supply links. So the Variables must be residing in the formula bar. Or if you have a better suggestion, please advise.
Best regards
Vilas Desai

[HansV]

If you cannot use links to another workbook, and you don't want to use a macro, I can only suggest that you copy the worksheet from the LT_Comparisons workbook into the Datasheet workbook.

[vilas desai]

Thanks Hans. Would this be another option that I have all the parameters (all options) in the Datasheet and show or hide those according to the selection made?

[HansV]

That would require VBA.

[vilas desai]

hmmm Seems no easy way out for me here...
Thanks a lot Hans Best regards

[HansV]

I recommend copying the sheet from LT_Comparisons into the Datasheet workbook. The formulas in the version that I posted can then be changed to a link to the other worksheet, so you wouldn't need external links, nor VBA.

[vilas desai]

I guess that should be the best option Sir. May be we can hide the ws LT_Comparisons (of course without a Macro)
Can You please help me with this and may be Lock it with a pw. Hope it will not be a part of print out


Thanks

[HansV]

In the attached version, the comparisons sheet has been moved to the Datasheet workbook and it has been hidden.
The workbook has been protected for structure, so that only users who know the password can unhide the sheet.
I used vilas as password, but you should change it to a more secure one.

Datasheet.xlsx

[vilas desai]

Great Thanks. Let me review it now

[vilas desai]

Thanks Hans, but I am still not able to use it. I get #Name in all cells when I make a different selection

[vilas desai]

=INDEX(comparisons!F4:BT4,_xlfn.SWITCH(LEFT($T$4),"C",1,"F",14,"D",27,"N",40,"U",53,"R",67))&""

How do I understand this formula Hans. Looks so complex

[vilas desai]

Do I have to use Office365 Subscription? All users may not have this subscriptions!

[HansV]

Here is a version that should work in all versions of Excel.

Datasheet.xlsx

[vilas desai]

Perfect, and Thanks a Lot Hans. Highly appreciated.
Is there a way to protect and hide the formulas or the formula bar itself with a password