Conditional data entry WITHOUT using a Macro
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Conditional data entry WITHOUT using a Macro
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
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
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional data entry WITHOUT using a Macro
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.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Conditional data entry WITHOUT using a Macro
I am sorry Sir. Here it is
Best regards
Vilas Desai
Best regards
Vilas Desai
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional data entry WITHOUT using a Macro
See the attached version. You may have to change the source of the links.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Conditional data entry WITHOUT using a Macro
Thanks Hans. I actually anted to use If / Then / Else IF kind of statements. Is that something which I am optimistic about?
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Conditional data entry WITHOUT using a Macro
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
Best regards
Vilas Desai
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional data entry WITHOUT using a Macro
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.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Conditional data entry WITHOUT using a Macro
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?
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Conditional data entry WITHOUT using a Macro
hmmm Seems no easy way out for me here...
Thanks a lot Hans Best regards
Thanks a lot Hans Best regards
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional data entry WITHOUT using a Macro
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.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Conditional data entry WITHOUT using a Macro
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
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
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional data entry WITHOUT using a Macro
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.
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Conditional data entry WITHOUT using a Macro
Great Thanks. Let me review it now
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Conditional data entry WITHOUT using a Macro
Thanks Hans, but I am still not able to use it. I get #Name in all cells when I make a different selection
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Conditional data entry WITHOUT using a Macro
=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
How do I understand this formula Hans. Looks so complex
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Conditional data entry WITHOUT using a Macro
Do I have to use Office365 Subscription? All users may not have this subscriptions!
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional data entry WITHOUT using a Macro
Here is a version that should work in all versions of Excel.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Conditional data entry WITHOUT using a Macro
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
Is there a way to protect and hide the formulas or the formula bar itself with a password