[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Dynamic Question Identification

Posted on 2011-05-02
35
Medium Priority
?
372 Views
Last Modified: 2012-05-11
I have a great little Worksheet written by Dave (dlmille) that needs some minor modifications.

1.) Move the Industry selection to the top.
2.) I need to have the data below and to the right of the Field Sorts so that I can easily "Insert" additional rows in the event of expanding the set of questions.
3.) I need to replace "Structured" and "Unstructured" with Maturity Level as a search criteria.
4.) Move D and E Columns below Row 10 and move the sort criteria to D and E columns above Row 10 with the 3 buttons.
5.) On the output (i.e. when you make the selection), I need to have the fields "Mat.Lvl.", "Type", "LOB" and "Business Scenario" show up before the Question Column.

That's it!

B.
Copy-of-Questions-for-Model-v4.xlsm
0
Comment
Question by:Bright01
  • 18
  • 17
35 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 35509078
Question:  What are "Field Sorts"?  Its easier to put the sorts on another sheet (original implementation before request to put on one sheet) than to put data below query results which could overwrite the data, potentially....

Approach - with some editorial license:

1.  I hit the Developer's ribbon DESIGN Mode to keep macros from running.  Then, I selected range A2:B2 and cut, selected A1:B1 and pasted with insert cut cells option
2.  Insert Questions by Selecting the Range in the Question Set (Columns F:L) by selecting a cell where you want to insert that row, in Column F, and drag mouse to column L - where F thru L are selected at the row to insert, then click right mouse button and "Insert", Shift down.
3.  Changed Row A2 (Structured) to "Maturity".  Went to the Define Names manager and pulled up Structured_List and copied that formula,  Hit NEW for new range name, entered "Maturity_List" and pasted that formula in.  Then I changed the header at cell F1, AAB1, AAB3, and AAL1 to "Maturity".
4.  Before I make this change, can you advise the rationale for this (for my understanding of how the system functions)?
5.  Currently, from these changes, we have Industry, Maturity, Type, LOB, and Scenario.  Are you dropping Industry?

Let me know and I can finalize.

Cheers,

Dave
0
 

Author Comment

by:Bright01
ID: 35509143
I'm not sure I follow the approach.  However,

1.) ?
2.) Can I do an "insert" (20 cells at a time) and the "range will maintain itself"?  This is the reason why I'm trying to insure the data is below the top 10 rows.
3.) Good....structured now moves to Maturity Level.
4.) The reason for this is to be able to insert new rows by making the data "independent" within the same tab.
5.) I'm not dropping Industry.  It's still a sorted field but there is no need to identify the questions that are associated with the industry since it will be the first field selected.  So if I pick Telecom, all the rest of the sorts will be Telecom...so no need to put into the output.

Does that make sense?

B.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35509195
1.  re: Approach:   just telling you want I'm doing to make the changes you request

2.  You can insert as many rows as you'd like - just ensure your selection is between F: and L:.  For example, to insert 10 questions, select F2:L12, then click right mouse button and insert, shift cells down.  Add the data you want and then use the Data Filter for sorting your question set, etc.

3.  Ok

4.  So, the proposed approack (re: #2 above) should suffice, yes?  Could also easily add your questions to the bottom, then sort the data anyway you'd like, so really no need to insert rows, but if you insert, selecting range F:L ok?

5.  I'm a bit confused by what you're calling "Output", can you elaborate?  Where is it in the existing sheet?  Is it the Question/Answer area in D:E or the QuestionSet with Answers in F:L?

Dave
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

Author Comment

by:Bright01
ID: 35509227
1. got it
2. sure.  But I cannot select the rows and insert....need to insert between F and L....ok.
3. OK
4. yep.  But think that having the sort selections at top and the rest below....seems logical if easy to do.
5. Output is the Questions that are identified/selected based on the criteria. It's in D:E with the additions of the other identifiers.  Make sense??

B.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35509618
Ok - all changes requested made.  I was able to put the autofilter/dv list info from row 50000, and moved the question set down to row 20, so you can now insert as many rows as you want in the questions.  Be sure to clear current before inserting rows.

A good practice when you're making spreadsheet changes is to go to the Developer's ribbon, then hit the design triangle, make your changes, then hit the design triangle to get out of design mode.  That way nothing fires while your trying to make changes.

Enjoy!

Dave
Questions-for-Model-v4-r3-bigmod.xlsm
0
 

Author Comment

by:Bright01
ID: 35509680
Dave,

I'll look over..... you're the best.  

B.
0
 

Author Comment

by:Bright01
ID: 35509693
OK...I get a debug error right off the bat;


 Set cboTemp = ws.OLEObjects("TempCombo")

Help?

B.

0
 

Author Comment

by:Bright01
ID: 35509704
Also, we should put the search criteria below the search criteria.

B.
0
 

Author Comment

by:Bright01
ID: 35509708
Sorry....we should put the search criteria above the search results........
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35509736
what is your definition of search criteria?  where is it now?

I just downloaded and its working here.

Try this again - see revised file attachment.  Let me know exactly what you did to get the error you're flagging.  Thanks!

Cheers,


Dave
Questions-for-Model-v4-r3-bigmod.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35509748
You want the yellow drop downs above the results of the selections?  going across?

Dave
0
 

Author Comment

by:Bright01
ID: 35509756
Dave,

Search criteria includes all of the fields we search on.


Here is my error on download;

Set cboTemp = ws.OLEObjects("TempCombo")

This is the error I get when I initially download and open.

and;

questionSet.AdvancedFilter Action:=xlFilterCopy, criteriaRange:=MycriteriaRange, _
            CopyToRange:=outputRange, Unique:=False

Let's think of this from a Business perspective.  We want to select a Industry, a set of criteria and have a set of questions present themselves. Right now, I'm getting a bunch of debug errors...........not sure what they are referencing.

B.

0
 
LVL 42

Expert Comment

by:dlmille
ID: 35509809
not a problem, just dealing with terminology, lol...

do you have Skype?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35509822
Is this the layout you're looking for - with the selections above the results of the selections?

Let's then work this "bug" you have that for some reason I'm not seeing...

Dave
Questions-for-Model-v4-r4-bigmod.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35509825
Be sure to select the file and save it, THEN open it.  Let me know...

Dave
0
 

Author Comment

by:Bright01
ID: 35509835
I did.... I can't get it to work at all. Let me know what I'm doing wrong...................... the errors are what I showed before.

B.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35509841
I must be really dense today, sorry.  I see you wanted to select Industry...  So, it might be useful for you to do a quick picture / printscreen to show me how you want this last big, re: criteria, laid out so we can both be more productive!

Cheers,

dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35509866
Does the old version work?  Get out and back into Excel first and try again?

Do you have Skype?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35509936
Ok.  Here it is with Industry -> the rest of the selections.  But the industry is not in the result list as requested.

Dave

If you errors are persistent, please do a screen capture and post online.  Tell me what the error message is.  And, if you have Skype, we can resolve quickly as well.

Cheers,

dave
Questions-for-Model-v4-r5-bigmod.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35510021
Bright - I think I have it - what caused the error.  Thanks to another colleague, we banged away at it.

You hit clear all before you did anything, right?  I did have a bug with that  (as a result of moving everything around).

All fixed and hopefully fixed for you!

Please advise.

Enjoy!

Dave
Questions-for-Model-v4-r6-bigmod.xlsm
0
 

Author Comment

by:Bright01
ID: 35512445
Dave,

This looks pretty darn good.  Except, I'm still getting an error.  I tried to expand the cells and increase the size of the type but this is the error I got.  Hopefully it is something rather simple.

B.
Error-report.pptx
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35514557
It is a big spreadsheet...  Did you go into design mode before making changes in the spreadsheet?

Go to the Developer Tab, then click the triangle.

Try to make the changes you'd like and advise.

When ready, just click the triangle again to be in "run" mode.

Cheers,

Dave
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 35514615
I can keep this particular "bug" from happening, however.  The Stack Overflow (and thankfully, you shared the code as well) is a result of referencing Target.Count which Microsoft in their wizdom must have dimensioned something less than all the cells you selected.

So, for this particular error, I coded to trap the error - you won't see it happen, again.

Cheers,

Dave
Questions-for-Model-v4-r7-bigmod.xlsm
0
 

Author Closing Comment

by:Bright01
ID: 35514724
Great job Dave!  It works very well.... testing it now.  You are so quick and have a great way of understanding exactly what someone is looking for.  You are a true technical professional.

Best regards,

B.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35515521
Thanks.  

I wanted to add one more enhancement - allow you to move the yellow cells anywhere you want, for future formatting...

If you go into Design mode, you can drag the yellow cells anywhere you want, in future.

Use this version which allows that to happen.

Cheers,

Dave
Questions-for-Model-v4-r8-bigmod.xlsm
0
 

Author Comment

by:Bright01
ID: 35515684
Great Dave!!  Again, "much thanks!"

B.
0
 

Author Comment

by:Bright01
ID: 35516992
Dave,

Quick question;  Can you comment on how I change the font on this model?  For example if I want it to be Arial 14pt. how do I make the change in both the Q/A and the drop down boxes?

Much thanks,

B.


0
 

Author Comment

by:Bright01
ID: 35517016
Also,  on the point of adding rows, you had said OK as long as I was adding them between F and L.  Don't you mean K and P?

B.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35517501
Changing font - just select the cells, columns, or rows, and make the font change. (go into Design mode before you start, so the drop downs don't activate until you get out of Design mode).

Change font - Home tab, see Font on the left side, just select font and size...

-----------------  Adding rows....

With this version, you don't have that restriction - I moved all the data around like you asked.

Without further modification, here are the "can" and "can'ts":

1.  The Q/A results from filters must start on Row 3 - as I hardcoded, the results coming in on Row 4 - pretty easy to change, but didn't as I was focused on all the other changes.
2.  The yellow criteria/filter cells can be dragged and dropped anywhere (just enter design mode, first!)
3.  You can insert rows anywhere below row 4, I believe, however,
4.  Don't delete the data setup for the filters (sitting below the questions, around row 50,000! - and that position moves based on row deletion)

To insert a row, just select the entire row and insert.  You can also delete rows in the same manner, but not so many, you lose the datafilter rows at the bottom of your spreadsheet - again around 50,000.  just goto Range "Criteria" to see where that is...

Dave
0
 

Author Comment

by:Bright01
ID: 35688489
Perfect!  Thanks.  I'll be integrating it into the master file today and will let you know how it goes.  Much thanks for a great little macro/app.  
0
 

Author Comment

by:Bright01
ID: 35688497
Dave,

One issue (and I'm happy to craft a related question if this is material);  When I add another industry, it doesn't identify those questions to that industry.  I may not have been clear on this industry criteria.  What I'm doing is adding additional questions by Industry; When you select the industry, you get those related questions. So Industry doesn't have to show up in the results but it is a sort criteria.

Let me know what you'd like me to do.

B.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35688526
Repaired - had a reference error - needed to test the Industry pulldown having made changes like you and missed a reference...

If you go to range "Criteria" - the cell underneath Industry should reference row 2 not 1.

=IF(Questions!$B2=0,"",Questions!$B2)

see attached...

lol


Dave
Questions-for-Model-v4-r7.xlsm
0
 

Author Comment

by:Bright01
ID: 35688594
Wow...that was an easy fix.....even a caveman could do it!  This app/macro is very cool.

B.
0
 

Author Comment

by:Bright01
ID: 35688920
Dave,

I'm really getting into this!  If I understand how you have designed/architected this, I can;

1.) add up to 49,999 questions without adding a single row
2.) I can add filters to the columns to help view which question areas I may be missing
3.) I can then sort the contiguous cells to get them into the right table positions

And I can do all of this without corrupting the macros!  This is way cool.  Have I missed something?

B.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35691204
Don't think so.

Dave
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question