• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Instead of Data Validation for Selecting value or text.......transpose maybe

Prinz Jaymes

Prinz Jaymes

Member
I want to administer some tests to my students.
Instead of them selecting answers from drop - using Data Validation,
Is there some way for them to
1. Select Cell the answer is supposed to go into and the PICK the answer from , say a LIST BOX (VBA)?

Q2. Ans:............................

Pretty much, is there a way to create an ANSWER LOG...
where if I select a blank cell or any cell ,say H15.......
I can now Go to my ANSWER LOG (list of all the answers) and ASK excel to CHOOSE whatever I picked in the ANSWER LOG to show in H15?

I know its doable, just don't know how.



p/s. Thanks to Chandoo, I have come to think more outside the box, not in terms of answers, but more in terms of possibilities. You Ninjas have made my World amazing with your solutions. So Thank u!!!
 
Hey,
The first method gives me data validation but in another format which is definitely what I do not want.
The second option is good but I prefer all the Answers to remain in answer LOG and not disappear after they have been selected.

Any thoughts?
 
I guess I'm not sure what you are trying to accomplish...what is it about a DataValidation or ComboBox that you don't want?
 
Hi Luke
Forget about data validation for a sec.

Is there a way, to click a particular value or "text' from a list and then it populates a different cell?

For example, If I am taking a Test,
if my answer is supposed to go into a blank CELL H5..........but the list of answers is in a 'list Box' ,
Is there way to
CLICK H5 (which is blank)
go to "List Box" (that has list of answer I have to pick)
and whatever I choose in List Box, is populated in CELL H5?

Hope this clarifies it.
 
Hi Jeffrey
I have been on this forum long enough to always think outside the box.

Using DV is ok, but I have see magic on this forum with amazing tricks or techniques.

Thats why I am enouraged to ask. The Excel Ninjas here appreciate the challenges.
 
And I've been around Excel long enough to have this as my mantra:

Lord grant me the VBA skills to automate the things I cannot easily change; the knowledge to leverage fully off the inbuilt features that I can; and wisdom to know the difference.


Excel Ninja Luke above asks:
I guess I'm not sure what you are trying to accomplish...what is it about a DataValidation or ComboBox that you don't want?

But you still haven't answered his question. So while Excel Ninjas like challenges, you've got to bear in mind that this is a pretty busy help forum, and ninjas are spread pretty thin helping. So for questions like this, it's better to be explicit, along the lines of:

"Hey, I know this can be done with DV, but I'm curious as to whether any other methods are available, and what benefits - if any - they have over DV"
 
Jeffrey
Not sure why you are short with me.
My answers so far indicate that I have used DV ; however, curious as to the possibilties of simply choosing from a list or list box and for the choice to be transposed into the Answer cell (for the Test).

Please be respectful! Sometimes it even takes us members a minute to truly determine what we are trying to achieve. Chandoo.org sets standards, and thats all I am trying to achieve here.
 
I'm short with you because you say "The Excel Ninjas here appreciate the challenges". This isn't set out as a challenge, and Luke probably wasn't aware that you wanted alternatives to DV for the sake of it. And Luke has replied has asked you a question in this regards that you haven't responded to. It's a fair question, and one I asked myself.

So this imho doesn't show enough respect for the volunteers that answer question here, me being one of them.

Now, tell me why my reply was disrespectful. Did I call you a name? Did I make fun of you? Nope. I just pointed out that:
a) it is desirable to leverage off inbuilt features whevever you can, unless there's a good reason not to
b) you haven't told us of a good reason not to.
c) this is a pretty busy help forum, and helpers (which are not limited to Ninjas) are spread pretty thin helping. So if you ask a question just because you are curious, it's best practice to explicitly call that out.

I can tell you as someone with moderation rights on two forums who spends many hours a week answering questions on forums that trying to determine what users haven't explicitly told you is more of a frustration than a challenge.
 
Jeffrey,
You are barking up the wrong tree. I am here to see if there are solutions or alternatives and don t have to justify it to you.
You have made no attempt to address my issue except question why i need alternatives.
Pleade refrain from replying me at all unless you have a valid alternative to DV.

If anything, i owe Luke an apology for brainstorming and not being as clear as i needed to be but i have addressed that as well in my last post yo Luke
 
Please refrain from replying me at all unless you have a valid alternative to DV.
Click to expand...
Look, it's a free forum. You don't like what I write...then stick your hands over your eyes.

You have made no attempt to address my issue except question why i need alternatives.
Click to expand...

Questioning why an alternative is required is one of the key ways to come up with the best approach. Which is exactly what Luke did when he asked
I guess I'm not sure what you are trying to accomplish...what is it about a DataValidation or ComboBox that you don't want?
Click to expand...
.

Pardon me for trying to help, but I was trying to understand your requirements. I do that a lot here, as you can tell by the 668 messages (and counting) that appear under my avatar. I spend a lot of my time asking why people don't want to use DV, or PivotTables, or other aspects so that I can be sure I'm understanding their problem, and so that I can be sure that they're not discounting a particular avenue based on something that can be overcome.

I happen to know a thing or two about DV, as evidenced by these posts I wrote on Chandoo's blog:
http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/
http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/

I spend a lot of my free time writing up articles like that - and answering questions here - for complete strangers, for nothing other than the fact that I hate to see people struggling with Excel.

I'm not asking you for an apology or to justify why you don't want to use DV. I get it...you don't want to use DV because you want to know what else is possible. That became clear from your first respone to me. It wasn't clear to me before I asked that question.

So I asked my question to see if I could uncover something about those requirements that weren't clear to me in your original question and subsequent responses to Luke. I showed up to help.

I'm damned if I'm going to try and help you again. I'm sure you're just fine with that.
 
Jeffrey
Interestingly, i would appreciate your input regarding an alternative to the DV option.
Knowledge is something i appreciate and i set no limits on whom i can learn from.

We ve obviously gotten off on the wrong foot.
So, can we start again.
 
Hello Prinz,

See attached. Is this something you like to achieve? If yes, doing with form control has limits or difficult to set up, if you have more questions.... VBA or some one may have alternatives with form controls...I am a newbie in VBA.
 

Attachments

  • List.xlsx
    11 KB · Views: 8
Nice try Haseeb
I have employed that method as well but need Answers to each question and for them to remain unchanged after they are selected.

Luke, Haseeb, Jeffrey,
Please see the modified copy of the attachment.
 

Attachments

  • List.xlsm
    11.2 KB · Views: 5
As Jeff somewhat alluded to, the realm of what is possible is near infinite. there are many different techniques we could use. If we include the user of UserForms, it's up to your imagination as to what the look/feel will become. However, there are definitely options that should not be used because they are overly complex. It is this part that was causing me and (I think) Jeff to stumble over how to answer you question. While we may know of other solutions, they would be more complex than a simple DV, and so were lost as to where to go.

But, going back a bit, I think the first link I posted deserves a second read. While the technique demonstrated was for a ComboBox, you could also use a ListBox. See the attached which uses same technique, but with a different form. Double-click on one of your Ans cells, ListBox appears. User makes choice, and can either hit Enter, or click outside of box to make it disappear.
 

Attachments

  • List Popup.xlsm
    23.3 KB · Views: 10
Hi Luke,
I understand. I will use the approach you suggested. Thanks for the attachment.

You guys are awesome! That includes you too Jeffrey.
 
Hey Luke
I tested it out and still struggling. Initially , I used the range in the attachment, but the pop-up seems to freeze or not open correctly now.
I think it is because my range of answers is more than 400. That is to place in pop-up.

I made some 'range changes' in the code but the pop is not responding as well.
Please advise.
 
Hi Prinz,

I'm afriad I would need to see at least the code, preferably the workbook in order to advise properly. W/o seeing it, I would guess that you need to check the ListFillRange, or the Height property...
Also make sure that the range you are checking for double-click (where we do the Intersect(Target,Range(...)) Is Nothing bit) is correct
 
Hi Luke,
I checked as you advised.

Turns out that Freezing any row above where the pop-up answers begin freezes it as well.

Because I have over a hundred questions and answers, I do need the row freeze so Testers can scroll up and down the test.
Any thoughts?
 
I don't know what you mean. Other than the sheet, what is getting frozen? THe other idea is to change where the box appears. In the code, you'll see a spot where you set the .Top and .Left values. You could try tweaking those, make the ListBox appear in a different location.
 
Prinz, would a user form be of any use or am I barking up the wrong tree? You could then send the answer to anywhere you choose and keep your answers in lists.
 
Great thread and interesting discussion.

Since I had a few minutes to kill, I made a small workbook that can show userform (with answer choices) upon question selection. It automatically records the response to a database worksheet (along with a studentID, timestamp) for further logging or analysis.

screen-0117.png

Check it out and feel free to customize.
 

Attachments

  • question-paper.xlsm
    28.2 KB · Views: 6
You must log in or register to reply here.
Back
Top