Dynamic-Table-Lookup-Answers Added

I have a great little macro well written by SiddharthRout.  I need an enhancement to it.  Presently, this macro takes a list of questions and has a dynamic sorting capability with three drop down boxes.  What I need is the ability to put answers in (Cell E) and a way of extracting (perhaps a summary button) the Questions within the same sheet, that are answered and the answers that are rendered.

Attached is the file.  Questions for clarity are welcomed.

B. Dynamic-Table-Lookup-scaled-v2-5.xls
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SiddharthRoutCommented:
B

I just have 10 hrs in my hand before I loose my internet connection :)

I am shifting to a new house. I will only be able to come online on Tue or Wed next week. So let's try and solve it in the next few hours.

Let me go though your query above.

Sid
SiddharthRoutCommented:
B

Where are the answers that needs to go into Col E?

Sid
Bright01Author Commented:
Ha...great question!  The workbook is used as a question capture tool.  That means that the questions come from interviews.  I need to explain a little more about the "use".  So you select the questions to ask by the use of the three fields.  Then as the qustions are answered, you put the answers in Col. E.  Extract the Q and A for interpretation and then clear/reset both Q and A.  The original Questions are still in the table but the answers have been reset for the next interview.

Does that help?

B.
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Bright01Author Commented:
Sorry; "an answer" capture tool.

B.
SiddharthRoutCommented:
So the answers are typed realtime?

Sid
Bright01Author Commented:
Yes.  So what has to happen are that the answers are recorded next to the questions (in the table)and when you clear and reset with the existing marco, it resets the question display (answers are only displayed if they are in the table (recorded).  Then you need to be able  to extract only those questions that were answered and the corresponding answers.  Then clear the answers (separate button).  I was going to put in the second button to clear answer field in the table (fairly simple I believe).

Hope that helps.

B.
SiddharthRoutCommented:
Ok understood.

Two questions.

and when you clear and reset with the existing marco, it resets the question display (answers are only displayed if they are in the table (recorded).

1)  Do you want the answers to clear as well?

2) Where do you want to create the summary? Do you have any specific format?

Sid
Bright01Author Commented:
The answers that are stored in the table are cleared on a separate button.  That way you can bring in new questions with the category select and answer them; collecting all of them in the table.

I don't have a specific format but I need it contained in the same worksheet if possible; in the up front columns if possible.

Thank you!

B.
SiddharthRoutCommented:
>>>That way you can bring in new questions with the category select and answer them; collecting all of them in the table.

B, but the moment, you select the a choice from the drop down list, the questions will be cleared and only the answers will remain which would be pointless?

Sid
Bright01Author Commented:
Sid,

Sorry for the delay...and I understand you may be out of pocket for some time.  

Let me explain.  When you open the file you have a table of questions and our three categories -- Type, LOB and Bus. Scenario.  When you click POPULATE you can then select as we have discussed.  You then can add an ANSWER next to the cell that contains THE QUESTION.  When you do that, it should not only populate the cell you type the answer into, but it should populate the cell IN THE TABLE that's next to the appropriate QUESTION.  When you Clear the Question and reset by Populating and again selecting, the Questions and Answers that are up front are cleared.  New questions can be selected the same way.  HOWEVER, the Answers should be stored in an Answer column in the TABLE.  From there, you can display (at any point) the Questions and Answers with the Questions that have been answered.  Then another button clears the Answers from the TABLE so you can start completely over.

Does that make sense?

B.

SiddharthRoutCommented:
Bright01Author Commented:
Very good!  Very close!

So when you hit Clear (probably should be called "Clear Current"), that clears both columns D and E at the same time.  The Data (Answers) in Column K stays as a holder (Questions and Answers).  Then if you hit "Clear All", it should clear D, E and K (Questions stay in table, are erased in D and all answers are cleared).  Probably needs a warning "This will clear all Answers".   When we have that right, I'll ask a related question to address the output view. We're going to need 3 more columns after E (not including F) -- so actually 4 columns.

Thank you!  I thought you were moving??

B.
SiddharthRoutCommented:
Yup waiting for the "Movers and Packers" to come.

Let me make those changes.

Sid

SiddharthRoutCommented:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
You are unbelieveable.  That works.  I'm going to incorporate it into the production model and see that it works.  I'm on the way abroad tomorrow......... and will send a related question for the output.

Much thanks...... you're really very very good.

B.
SiddharthRoutCommented:
Glad to be of help. I would be able to answer your question only next week now :)

Sid
Bright01Author Commented:
Thanks again Sid.  I've been working with the model and it's quite a piece of work.  I've added data to one of the columns ( I could not add a column due to the complex coding), but after hours was only able to get the drop downs to populate.  I could not get the Questions to load by registering the answer (S or U) in the first box (I created).

Anyway, asked a related question.... hope you can pick it up.

Hope all is well,

Best regards,

B.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.