Re: Choose text from drop down list will populate lists of texts automaticaly in next
Theoretically possible, but very messy.
Using VBA it is POSSIBLE, you need to include a "please select" or Please choose" or similar in the list, and implement code on the worksheetchange event to populate cells when something is added (more information can be provided if this is the way you want to go)
Without VBA, you COULD have several dropdown menus across row 3 (B3, D3, F3 for example) and formulae to fill the table based on the values in those boxes (more information can be provided if this is the way you want to go).
Re: Choose text from drop down list will populate lists of texts automaticaly in next
This code should be on sheet1. Additionally you will need to set B3 to the named ranged "meeting", and the names on sheet2 to the named ranges "Enviroment", "Audit", and "Planning" (Ideally these should be dynamic named ranges, but if you're unsure normal ones will work.
It SHOULD be fairly obvious how to extend this for more categories etc.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rdata As Range
Dim c As Range
If Target.Address = ThisWorkbook.Names("meeting").RefersToRange.Address Then
'If the dropdown was changed
Select Case Target.Value
Case "Environment and Community"
Set rdata = ThisWorkbook.Names("Environment").RefersToRange
Case "Audit and Risk"
Set rdata = ThisWorkbook.Names("Audit").RefersToRange
Case "Planning"
Set rdata = ThisWorkbook.Names("Planning").RefersToRange
End Select
'Set rdata to the range of names to add
For Each c In rdata.Cells
If Target.Parent.Cells(65000, 1).End(xlUp).Row < 23 Then 'If there is still space in the table
'If the name is not ALREADY present, add it to the last line
If Target.Parent.Columns(1).Find(what:=c.Value) Is Nothing Then Target.Parent.Cells(65000, 1).End(xlUp).Offset(1, 0).Value = c.Value
End If
Next
End If
End SubRe: Choose text from drop down list will populate lists of texts automaticaly in next
Hi Sainaa
Here is another way:
Put the following formula in A7:
=HLOOKUP($B$3,Sheet2!$A$1:$I$23,ROW()-4,0)
Now highlight A7 to A23 and make the Cell Format equal to this:
0;-0;;@
Take care
Smallman
Re: Choose text from drop down list will populate lists of texts automaticaly in next
I love the cellformat trick, I haven't seen that before.
Unfortunately for the rest of that, my understanding of the requirements was that changing the drop down would ADD names to the bottom of the table, not replace ones that are there, making a non-vba solution impossible (without multiple drop down boxes)
Re: Choose text from drop down list will populate lists of texts automaticaly in next
Thank you Smallman,
But it is keep replacing the ones everytime I change the drop down. Do you know any other way?
The requirements are that changing the drop down would ADD names to the bottom of the table, not replace ones that are there,
Sainaa
Re: Choose text from drop down list will populate lists of texts automaticaly in next
Hi Richadj4,
What you mean " to set B3 to the named ranges " Meeting"
B3 got my drop down list!?
Thank you, sorry for being a pain... 
Re: Choose text from drop down list will populate lists of texts automaticaly in next
Delete the "*" you currently have in cells A7 - A15, Then it should add things from A7 downwards. Currently it stops at 23 (cause that the size of the table), If you want it to go further then change
[COLOR=blue]If[/COLOR][COLOR=#333333] Target.Parent.Cells(65000, 1).End(xlUp).Row < 23 [/COLOR][COLOR=blue]Then[/COLOR]
this to a number higher than 23, or delete it (and the endif) entirely
Re: Choose text from drop down list will populate lists of texts automaticaly in next
Thank you,
I removed the "*" and it works fine, THANK YOU.
However I do NOT need any colouring in A column, also Name repeats condition is not required as some attendees attend more than 1 meetings. So actually I need names repeat is ok condition!!? I am getting there with your help...
Re: Choose text from drop down list will populate lists of texts automaticaly in next
uhhhhhh, coloring was already there? nothing to do with the code, just change the fill to nofill
If you WANT repeated names, just delete this:
If Target.Parent.Columns(1).Find(what:=c.Value) Is Nothing Then
(leave the rest of that line)
Re: Choose text from drop down list will populate lists of texts automaticaly in next
"However I do NOT need any colouring in A column" - The is no colouring in the code. If the colouring in column A (The purple and yellow) should not be there, then it should be manually removed by changing the fill of the cells in excel, nothing to do with VBA
On a completely unrelated note, if you want the table to fill down to more than row 23, then change the 23 in the code to a higher number
If Target.Parent.Cells(65000, 1).End(xlUp).Row < 23 ThenOR (for unlimited rows) delete that line entirely. ( you will also NEED to delete the FIRST endif below it).
Re: Choose text from drop down list will populate lists of texts automaticaly in next
Not a problem
No unfortunately
There should be a link at the bottom of the post where you can "give reputation" for a helpful response.
Re: Choose text from drop down list will populate lists of texts automaticaly in next
I see, did not know there is a feedback button as I recently joined to this website!! Now I know!
THANK YOU big big time!
Take care
Sainaa
Don’t have an account yet? Register yourself now and be a part of our community!