How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Software Engineer
Category: Programming
Satisfied Customers: 1845
Experience:  Extensive Knowledge in PHP, MYSQL, CSS & Javascript
62934938
Type Your Programming Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

I have a sheet with 2 columns: various different names and a

Customer Question

I have a sheet with 2 columns: various different names and a column with 1 of 6 different IDs. I want to create a Data Validation on another page that lists ONLY the names of each of the 6 different IDs in the dropdown. How can this be best accomplished? Thank you.
Submitted: 10 months ago.
Category: Programming
Expert:  Jason Jones replied 10 months ago.

Hello,

I am the person that will be helping you today. May I take a look at the document you need help with so that I can complete this task for you?

Please, be forewarned that the site is not secure:
- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"
I will be standing by.

Thank you,
Jason

Customer: replied 10 months ago.
I have attached the sample file here. Can you work with this? Thanks.
Expert:  Jason Jones replied 10 months ago.

Thank you. I will start working on this.

Customer: replied 10 months ago.
I clicked the button for a call but don't really need this. If you can insert the proper formulas into the sample file and email me back, that would be sufficient. Thanks.
Expert:  Jason Jones replied 10 months ago.

Thank you for the update.

Expert:  Jason Jones replied 10 months ago.

I am looking at the sheet now. Please, explain what you need for this document to do.

Customer: replied 10 months ago.
I'm logging off now. Will you email me tomorrow?
Customer: replied 10 months ago.
I want to create a Data Validation on the second page that lists ONLY the names of each of the 6 different IDs listed, in the dropdown. See the file for info.
Expert:  Jason Jones replied 10 months ago.

I honestly, do not understand. You wish to choose one of the values in the dropdown box and have the resulting items that match, be entered into the 2nd sheet?

Customer: replied 10 months ago.
On the second page (Sheet1), in cell A6, I want a dropdown list of all the names in column B on the Manager Info tab where column A (AllocationAproach) is the letter C. Then in cell B6 on Sheet1, I want a dropdown list of all the names in column B on the Manager Info tab where column A (AllocationAproach) is the letter COMB. Etc. Etc. Ok?
Expert:  Jason Jones replied 10 months ago.

I got it. Thank you for the clarification. I will have this fixed and uploaded soon.

Expert:  Jason Jones replied 10 months ago.

Do you need a macro of some sort to be setup for this or do you just need me to gather the values as you described and create the sheet for you?

Customer: replied 10 months ago.
Ideally, I just want the formula in cell B6. If this requires some other interim formulas, put them on the file too. Thanks.
Expert:  Jason Jones replied 10 months ago.

You wish to have this done in A6 to G6, correct?

Expert:  Jason Jones replied 10 months ago.

I have run out of ideas.

For this reason, I am opting out of the question and allowing another expert the chance to help.

The next expert will see all that is on this page, so there will be no need to repeat anything.

When another expert picks this up, you will be notified by email.

Thank you for your patience.

- Jason

Expert:  The-PC-Guy replied 10 months ago.

hello, I will be helping you with this.

Just so I understand you want to have multiple drop downs, one that lists the values for each of the unique values in column A. Is this correct?

Also I will need to know which version of excel you are running

Thanks

Customer: replied 10 months ago.
On the second page (Sheet1) of the attached sample file, in cell A6, I want a dropdown list of all the names in column B on the Manager Info tab where column A (AllocationAproach) is the letter C. Then in cell B6 on Sheet1, I want a dropdown list of all the names in column B on the Manager Info tab where column A (AllocationAproach) is the letter COMB. Etc. Etc. Ok?
Expert:  The-PC-Guy replied 10 months ago.

can you tell me which version of excel you are using?

Customer: replied 10 months ago.
Excel 2013
Expert:  The-PC-Guy replied 10 months ago.

ok, then a macro will work.

You should understand the following.

1. There is no formula that will do this. The way data validation lists work is by using a range of cells with values.

2. A macro can be used to sort and filter this data into several lists and then create the drop downs for you.

3. The macro can be put in so that every time you open the workbook it runs for you and updates the lists incase you add more data. or a button can be added that you push

to update the data.

4. There will be an additional fee to create such a macro.

Does this sound agreeable?

Customer: replied 10 months ago.
Are you saying there is no formula that can look at an entire list and only look for the letter C (like a VLOOKUP or something) and then return a list of all the names that meet that condition? What would the macro do? Sort the list, create a NAME that would then be assigned to the Data Validation?
Expert:  The-PC-Guy replied 10 months ago.

a vlookup would not work as it would only give you the first value that matches.

So the macro would create separate lists for all the values matching each letter. Instead of only the first result that vlookup would do. The macro could also create the data validation lists themselves from those lists.

Think of it like having a bucket with different colored golf balls in it. THe macro would separate all of the red balls into a list, and all of the blue ones, and green ones, and so on.

Customer: replied 10 months ago.
Take a look at this newest sample file. On the Manager Info page, I've added columns that list all the Nicknames that are assigned to each allocation. Now the question is, how to build a DataValidation that eliminates the blank rows when creating the dropdown.
Expert:  The-PC-Guy replied 10 months ago.

unfortunately you are going to have the same problem. You will still need a macro to filter out the blanks.

You can sort all the columns By descending values before doing the lists, but some lists will still have blanks in the middle.

This was listed as a programming question. I suggest you strongly consider the macro, it will make things easier in the long run

Customer: replied 10 months ago.
But I saw a website last night on how to create a column, next to each column, that would eliminate the blanks. Then you could set the DataValidation to be off this column that has no blanks. There were no macros involved. Just a formula that eliminates the blanks. Are you aware of how to implement this?
Expert:  The-PC-Guy replied 10 months ago.

sorry, a formula willl not get rid of the blanks.

Not sure what you saw on some website, but there is a lot out there that is incorrect. As I said the formula would only return the first result, not all of them.

It is possible to eliminate the blanks by sorting the column, in essence putting all of the blanks at the bottom.

Customer: replied 10 months ago.
What about this:http://www.mrexcel.com/forum/excel-questions/635269-remove-blanks-data-validation-drop-down-list-condition.html
Expert:  The-PC-Guy replied 10 months ago.

do you have something about macros. Again much easier.

Customer: replied 10 months ago.
It's just that I'm giving this to a person that is not that Excel literate and am concerned about HIM executing the macros. I'm not that familiar with a macro but isn't that just an automation of some keystrokes that I would do to filter properly?
Expert:  The-PC-Guy replied 10 months ago.

sorry, a macro is so much more. It can actually program any function that you could do manually. Like copying and pasting all of the values into separate columns and generating drop downs from that.

I just don't see a formula working in this case.

The only thing with a macro is they don't run on MAC os. And some security settings in excel make it so you have to press a button at the top of the screen to enable macros when you open the document. Other than that the macro would be programmed to run when the workbook is opened. So other than enabling macros the person you give it to would not have to do anything else.

You asked for my expertise and I have been working with excel for over 20 years and know all the ins and outs. I do sometimes feel that macros will work in some circumstances where formulas will not.

Customer: replied 10 months ago.
Will I be able to see the steps that you include in the macro?
Expert:  The-PC-Guy replied 10 months ago.

you can view the code for the macro, but I am not sure you will understand it. Just know that the macro can do what you were looking for.