Jump to content
By fans, for fans. By fans, for fans. By fans, for fans.

Any Excel experts on here?


Recommended Posts

Thanks lads, each of your hints were nearly there, they're exactly the type of drop down lists I want, the only problem is the data contained :)


I suppose I should have gone into a bit more detail. It's to do with a traffic management system, so it's measuring how built up traffic is so for example there is a street here called Pearse St and when describing it people say it's backed up as far as Erne St or backed up as far as Macken St, Sandwith St etc.


I need to have a heading of "Pearse St and then be able to choose from a drop down list with options of Pearse>Erne, Pearse>Macken and Pearse>Macken but for reasons of space (It's for a lot of streets) I need the spreadsheet to just contain the cells with the heading and the list beneath it (If that makes sense)

Link to comment
Share on other sites



Perhaps I am misunderstanding the point, but I think the solution to your query is dependent on how the data in the spreadsheet is displayed.


Do you have one column that has the street that you are focussing on (i.e. sometimes Pearse St , sometimes street x, sometimes street y) and then in the next column the street name that the traffic is backed up to (i.e. Erne St or Macken St, Sandwith St)?


If so, you can use the Concatenate function to combine the data from the two columns (and add something between such as '>') to get one column which would hold the data you are looking for (and then you could just apply auto-filter to see all instances where (for example) traffic is from Pearse street to Erne street).



If you do have the data in this format, you would want to insert a new column (calling it combined streets, or what-ever makes sense to you and then use the formula =CONCATENATE(A2,">",B2) - changing the Cell reference so that in your example A2 relates to the cell containing Pearce street (or street x, y) and B2 would relate to the cell containing where the traffic is backed up to. And then you could copy down to the end of the spreadsheet the formula in this new column...


If your data is not displayed as in my example, a brief explanation of how the data is dispayed will make it easier to try finding a solution...

Edited by catinhat
Link to comment
Share on other sites

Guest DanTheDaggerman

I think I get what you need. For each street, there are a number of streets that the traffic could be backed up to. Is that correct?


If so, you could use conditional dropdowns. So, dropdown #1 contains the basic list of streets. The contents of dropdown #2 will change depending on what street is selected in dropdown #1.


There is a good article here that describes how to do that.

Link to comment
Share on other sites





The ">" just means what you want to be shown between the two street names. You could equally have " to " or indeed anything you like between the quotation marks (remembering to have a space before and afterwards if you are using a word)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...