Hello and Thanks for Choosing JustAnswer!
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.
Password XXXXX the Excel file I created for you is temp
You can download it here: http://www.mediafire.com/view/cgvdy92gp84bbgb/Test.xlsx
(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.
(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.
Let me know! :)
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!!
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.
Its a dummy
its doesnt exist
hence it fails just as we want it to
if cell is empty we set it to Categories range if not basically set it to nothing
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?
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.
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
I removed things such as print ranges etc etc
I wanted to simplify for you basically so you can see and focus on the dynamic listing
There is no dummy range
Its just a random name
You can call it whatever
It just has to be a range that does NOT exist
so INDIRECT fails
So all you need is the following
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.
I think it might be because I haven't named my ranges yet..hold on
(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 :)
in Data Validation pressing F8 should let you pick from existing categories
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.
wait...i think it's because my panes were frozen...hold on
Nope...same error message...another tech said spaces aren't allowed in ranges?? But yours seemed to work fine with spaces..
that is what I was explaining above - you have to use a nested SUBSTITUTE command and replace all special characters and spaces with "_"
than call the INDIRECT(T9)
ahhh! so I need to go into each cell in the top row of the worksheet and do this before "creating names from selection"
you create the ranges as is
just do the DATA VALIDATION formula with the SUBTITUTES
meant in M16
let me know if you can get it to work this time
okay...just a sec
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.
I'll send you the revised spreadsheet so you can take a look
select range by hand than press ctrl+shift+f3 n give it a name
it had Left Column already selected and I was able to click ok
moving on..one sec
i named my ranges by top column
i meant top row
that's what I thought, but when I select top row I get the error again
on the other hand...
when I select column A, it allows me to create
"Replace exising All_Subjects?"
now I'm getting the "Named range you specified cannot be found" when copying your formula into the DV
what did you name ur degrees as and what did you name your courses as?
so i make i explain correctly
degrees is named All_Subjects, courses are named by individual range names (i.e. Administration_of_Justice_AA)
you can always press ctrl+f3 and delete the ranges you do not want and do it again
that's what I was afraid to do at first...thinking if it didn't work, I'd have to start all over :-/
how many u did?
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
im not sure if i follow
you have 2 cells in SEP you need formulas in
rest of it
just create 1 degree column
and column for each class listing its courses under it
u create a range per column in data by hand
okay...thanks so much!