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 kooleraid Your Own Question

kooleraid
kooleraid, Consultant
Category: Microsoft Office
Satisfied Customers: 124
Experience:  Microsoft Office
40431348
Type Your Microsoft Office Question Here...
kooleraid is online now
A new question is answered every 9 seconds

I would like to create a macro in Excel that creates a drop

This answer was rated:

I would like to create a macro in Excel that creates a drop down list based on the value of another cell. My spreadsheet has a list of College subjects in a drop down list in cell (A2) using data validation. I would like cell (A1) to have a data validation list based on the value selected in cell (A2), and for this list to include Classes required for each College subject. I have defined names for each of the required classes on another sheet where I have collected all my data.

I have tried to use the INDIRECT formula in data validation, but this is resulting in an error. I will gladly attatch the worksheet for reference. I only need one example of the formula needed, as I know writing new VBA code can be complicated & expensive on this site. I would like to get an example of how to create this for the remainder of my data.

kooleraid :

Hello and Thanks for Choosing JustAnswer!

kooleraid :

You do NOT need a macro to do this. I checked your history with JustAnswer and see that you already provided your sample Excel document before so I took the liberty of downloading that document from your previous question and fixing it up for you so that this works as you desire.

kooleraid :

Password XXXXX the Excel file I created for you is temp

kooleraid :

You can download it here: http://www.mediafire.com/view/cgvdy92gp84bbgb/Test.xlsx

kooleraid :

(1) On the SEP sheet you can now pick a degree in T9 and then M16 will dynamically let you pick a course that belongs only to that selected degree.

kooleraid :

(2) I also took the liberty of advancing your logic in your request such that if you have done step 1 above and a course is selected in M16 - it will NOT allow you to change the selected degree in T9 until you clear out the selected course in M16 thereby making sure you can never end up with a course that does NOT belong to a degree.

kooleraid :

Let me know! :)

Customer:

Wow! I had figured out the formula that needed to be added to the Data Validation so that this operation would work, but I hadn't thought about restricting the change of Subject in M16 so as not to cause any confusion. Genius!!

Customer:

Wow! I had figured out the formula that needed to be added to the Data Validation so that this operation would work, but I hadn't thought about restricting the change of Subject in M16 so as not to cause any confusion. Genius!! Now, copying the formatting to adjacent cells was a bit challenging, and I noticed a =IF(M16="",Categories,INDIRECT("FakeRange")) in the DV of T9. What does this "FakeRange" refer to? I can't find it to duplicate it to other spreadsheets.

kooleraid :

Its a dummy

kooleraid :

:)

kooleraid :

its doesnt exist

kooleraid :

hence it fails just as we want it to

kooleraid :

if cell is empty we set it to Categories range if not basically set it to nothing

Customer:

Wow! I love it. Now, I will have to transfer additional data over to your revised document, since I can't input your dummy range into my existing doc. This won't disrupt the formula will it?

kooleraid :

When you select the degree range and tell it to name it using the 1st row what will happen is the special characters such as " " "-" "(" ")" will get converted to "_" characters therefore when you then do an INDIRECT from a course range back to it - it will fail because you would end up comparing degrees in original form with course title row containing "_" subtitutes hence why it will complain and not work before. At least that was the error in your original spreadsheet. Therefore, a nested SUBSTITUTE command needs to be used to also remove those characters before INDIRECT is invoked in DATA VALIDATION for M16 such that it works.

kooleraid :

Well I removed stuff from the sheet before I sent it to you so not sure if there was anything relevant that cut in the process or not

kooleraid :

I removed things such as print ranges etc etc

Customer:

that's fine

kooleraid :

I wanted to simplify for you basically so you can see and focus on the dynamic listing

kooleraid :

There is no dummy range

kooleraid :

Its just a random name

kooleraid :

You can call it whatever

kooleraid :

It just has to be a range that does NOT exist

kooleraid :

so INDIRECT fails

kooleraid :

So all you need is the following

Customer:

So now that I'm adding more data to it, it's not responding. I want to make sure I can continue to add content to the "Catagories" range and also to the classes without dirupting the formula.

Customer:

wait

Customer:

I think it might be because I haven't named my ranges yet..hold on

kooleraid :

(1) open your xls (2) press ctrl F3, (3) delete the ranges you do NOT need, (4) go to data tab (5) re-define your ranges ie press Ctrl+Shift+F3 (6) go to T9 cell on SEP (7) Press ALT+D+L (8) Choose List enter formula from my xls (9) go to M16 cell on SEP (10) Press ALT + D + L (11) Choose List enter formula from my xls (12) Test :)

kooleraid :

in Data Validation pressing F8 should let you pick from existing categories

Customer:

ok, i'm missing a step (4), it won't allow me to select top row "Selection is not valid: copy and paste area cannot overlap; If you are using the Create from selection command...; If the names you are creating are listed in a row...; etc.

Customer:

wait...i think it's because my panes were frozen...hold on

Customer:

Nope...same error message...another tech said spaces aren't allowed in ranges?? But yours seemed to work fine with spaces..

kooleraid :

that is what I was explaining above - you have to use a nested SUBSTITUTE command and replace all special characters and spaces with "_"

kooleraid :

than call the INDIRECT(T9)

Customer:

ahhh! so I need to go into each cell in the top row of the worksheet and do this before "creating names from selection"

kooleraid :

no no

kooleraid :

you create the ranges as is

kooleraid :

just do the DATA VALIDATION formula with the SUBTITUTES

kooleraid :

in T9

Customer:

oh! okay

kooleraid :

oh wait

kooleraid :

meant in M16

kooleraid :

:)

Customer:

right!

kooleraid :

let me know if you can get it to work this time

Customer:

okay...just a sec

Customer:

Still can't get past the error message when I attempt to "Create Names From Selection". Could it be because I'm not deleting ranges I don't need? Step (3)...Can't quite determine which ones I need and don't need.

Customer:

I'll send you the revised spreadsheet so you can take a look

kooleraid :

select range by hand than press ctrl+shift+f3 n give it a name

Customer:

ok...that worked

kooleraid :

:)

Customer:

it had Left Column already selected and I was able to click ok

Customer:

moving on..one sec

kooleraid :

i named my ranges by top column

kooleraid :

i meant top row

Customer:

that's what I thought, but when I select top row I get the error again

Customer:

on the other hand...

Customer:

when I select column A, it allows me to create

Customer:

"Replace exising All_Subjects?"

Customer:

OKAY

Customer:

now I'm getting the "Named range you specified cannot be found" when copying your formula into the DV

kooleraid :

what did you name ur degrees as and what did you name your courses as?

kooleraid :

so i make i explain correctly

Customer:

degrees is named All_Subjects, courses are named by individual range names (i.e. Administration_of_Justice_AA)

kooleraid :

ok

kooleraid :

you can always press ctrl+f3 and delete the ranges you do not want and do it again

Customer:

okay...

Customer:

that's what I was afraid to do at first...thinking if it didn't work, I'd have to start all over :-/

kooleraid :

how many u did?

Customer:

so far, about subjects, but as you can see, I have over 100 subjects. If I can get the formula to work I can just copy down to additional cells

Customer:

*13 subjects

kooleraid :

im not sure if i follow

kooleraid :

you have 2 cells in SEP you need formulas in

kooleraid :

rest of it

kooleraid :

just create 1 degree column

kooleraid :

and column for each class listing its courses under it

kooleraid :

thats it

kooleraid :

u create a range per column in data by hand

Customer:

okay...thanks so much!

kooleraid and other Microsoft Office Specialists are ready to help you