..
I looked at the file but don't understand what the problem is. could you please elaborate?
Thank you Phillip for running the file upload issue to ground.
For the file in question, I've a couple of questions based in curiosity rather than a problem.
1. For the dependent drop-down, is there a way to create the 'To' drop down such that its list doesn't contain the value chosen in the 'From' list? I've not been able to think of an elegant way to exclude the FROM variable from the TO list. As much as I hate to say it, my mind falls back to nested IFs but then I realize what kind of nightmare that would be to write and make correct.
2. In the context of the functions/formulas when writing it as a stand alone function, the CONVERT function requires the from & to arguments to be enclosed in quotes but when passing the arguments to the CONVERT function through another function the passed value has no quotes.
What is Excel doing in the background that turns an otherwise invalid argument into a valid argument?
Does Excel perform this same background function when passing a value to any function that requires special formatting in order to build a valid argument?
Regarding 1, I've added a named formula TOselection and referenced it in J1 as the source of the TO_value. Don't need the extra step but it's cleaner that way. Works as you intend, I believe.
Regarding 2, I don't believe there anything special going on in the background. When you hard-code a text value into an argument of any function (not only CONVERT) you must include "" to explicitly indicate that it is a text. Otherwise, Excel thinks you are entering a function, a named range, a cell reference or a number. But when you refer to a cell that contains a text, it's just that. A text with no need to add "".
For example, if you would write a formula =ISTEXT(1) you get FALSE. Write =ISTEXT("1") and you get TRUE. Suppose you enter a text into cell A1 and you write =ISTEXT(A1) you get TRUE since cell A1 contains a text.
Thank you Riny for the quick response.
I've seen the FILTER function discussed and read a bit about it but have never used it. I guess now I'll have to play around with it to see what all it can do and how I can incorporate it elsewhere.
On the other question, I wasn't even thinking of it in terms of a text variable I was providing to the function. I was thinking of it in terms of it being a variable, complete with quotes, that was built into the CONVERT function since that is how it populates via the auto complete menu. I originally built my table with the unit variables in quotes and was about to start banging my head against something because, no matter what I tried, I couldn't get it to work. At this point, I don't even remember why I tested passing the variable without quotes but it worked...I just wasn't sure why.
