Default value in a cell from data validation list

[mahfuz89]

Hi,

I am trying to show a default value in a cell from data validation list using VBA. It works if the list is in the same sheet (Sheet1) but it doesn't if the list is in other sheet (Fruits here).

Can anyone assist me to make it work from the list in other sheet, please?

With regards,
Md Mahfuzur Rahman

[torachan]

I found it difficult to understand fully what you were trying to achieve as there would have been a need to make a second choice dependant upon the first choice made - so have offered the following as I think this is what you are trying to do.
However I may be completely wrong ??????
Try the attached using 'structured tables' - all the data and choices you make stay within the confines of the table - there is no need to extend the data validation this occurs dynamically as the tables expand/contract.
Look in the 'name manager' to understand the process - the teacher name list is taken from the table headers - then a relationship is established between each column and an index is established to determine which column is read into the subject list.
Refer to the data validation setup and you will see the reference names that are called for the list of each validation.
To start the table first put a 'full stop' (point) in cell 'A2' move to 'B2' and back to 'A2' then make your two choices - to extend the table repeat in 'A3'>'B3'>'A3'

[mahfuz89]

Hi Torachan,

This does not go with my problem. I need it to select any value rather than default value. The value of first cell comes based on value of other cell which is also selectable.

Regards,
Md Mahfuzur Rahman

[torachan]

Explain a little clearer - your initial post quotes quite clearly---

I am trying to show a default value in a cell from data validation list

[mahfuz89]

Hi,

I want to show default value from validation list in one of my projects. Please see the sample file. Cell B4 is validated with drop down list. On every selection there comes the third number of objects from respective drop down list, later I can change the value from the list as I require. The default value comes if the drop down list lies in same sheet. It does not show the default value if the list lies in other sheet. Please check the "Fruits" and "Fruits1" items. Fruits1 shows the default value "Mango" in cell C4 from drop down list but Fruits does not show the default value "Mango" from the list in next sheet, the drop down list comes but default value is not shown.
I hope I was able to make you clear.

Regards,
Md Mahfuzur Rahman

[beyond Excel]

Hello, mahfuz89. Replace what you have with this other:

Public Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$4" Then Exit Sub
With Target(, 2)
  .Validation.Delete
  .Validation.Add Type:=xlValidateList, Formula1:="=" & Target
  .Value = ThisWorkbook.Names(Target.Value).RefersToRange(3)
End With
End Sub 

[mahfuz89]

Hello Beyond Excel,

I would like to convey my deepest gratitude for the invaluable support you provided me in resolving my recent problem. Your response and helpful solution made a huge difference in my project.

I was facing a difficult problem and was unsure of where to turn for help. However, the assistance provided by your forum was truly amazing. Your knowledge and expertise in the field helped me find a solution that I would never have been able to achieve on my own. The clarity and simplicity of the solution provided were greatly appreciated, as well as the promptness of the response. I am truly thankful for the effort you put in to help me out and for taking the time to share your knowledge and expertise.

Once again, thank you very much for your help. You have provided me with an invaluable service, and I will not hesitate to recommend your forum to others in need of similar assistance in the future. All the best.

E.g. I was trying to reply you on the day you sent me the code but the mail bounced back.

Sincerely,
Md Mahfuzur Rahman

[beyond Excel]

E.g. I was trying to reply you on the day you sent me the code but the mail bounced back.

Sincerely,
Md Mahfuzur Rahman

Mahfuzur: You have been so generous in your concepts and so grateful that... who cares to wait a little longer or a little less! It isn't true?...

Greetings and until next time.

[mahfuz89]

Hello,

Are you Craig Hatmaker from Beyond Excel? If this is you then I found you in you tube and great to see you.
Greetings to you as well. Regards,

[beyond Excel]

.

Are you Craig Hatmaker from Beyond Excel? If this is you then I found you in youtube and great to see you.

Not really: I'm much prettier and much younger... (Ja ja ja)

[mahfuz89]

Hello,

Greetings!
I need to get a default value from its drop down list in cell E11 based on the value in cell B11. The drop down list changes while changing the selection in cell C10 but the default value does not show, shows previous value. I have used a macro.
Please assist.

With regards,
Md Mahfuzur Rahman