Selecting a table based on drop down list condition

Hi everyone
I would like to create the following:
1. User selects some options from a drop down list.
2. Based on these options, a particular table is chosen. There may be several tables and ONE of them will be chosen i.e. excel jumps to the worksheet

Below is a general architecture of what I would like
http://s17.postimg.org/m3di7sedr/image.png

I have attached an excel file that contains the drop down list and tables on worksheets 3 and 4.

how do you determine which table is selected? I can see what you have on sheet1, but can find no "link/match" to that on sheet3/4.

It will make it easier to offer suggestions if you could show us what your expected outcome is (even if you enter it manually) and how you arrived at that?

thanks for your response. I see what you mean.
I have attached another excel file.
First, a user has selected Terrain: Large lake/sea, Antenna configuration: TRDR_TRDR and placement: 1.8/1.8
These options are linked to sheet 3 as per the small table G1:I2.
Since these two match, then the big table on sheet 3 (A1:E23) should be copied to Sheet 1.

Im thinking that because your tables are not exactly the same shape/format, it might be easier to "jump" to the required table, rather than tryo to replicate it.

Is this something you could work with?

Jumping can be done using worksheet function itself but it will becomes hectic approach when the template numbers are more since you are maintaining individual sheets for each templates.

Please confirm which approach you would like to have VBA solution OR using worksheet functions?

Then Please Confirm What will be the maximum Number of Templates you will create in each sheets?

Oopss... sorry for asking too many questions... Last question what is the maximum number of sheets you will create?

No issues... We go for VBA solution which will work with any Number of sheets. But please ensure that you should keep the condition area in G1 to I2 in each sheet (which you did in sheet3) for identification of the data.

Copy the below code and Do Right Click On Sheet1 Sheet Tab and select view code and paste the code and return to excel.

Do Double click on Sheet1-A1 cell which will run the macro based on the selection made in A2,A4,A6 cell.

Please Login or Register  to view this content.

thank you sir it worked perfectly.
Seems like coding in excel is a very powerful tool!

Just a quick quesiton:
What does these two lines do?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) - code runs only when double clicked?

If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub -- ??

Thanks for the assist Six

Glad it helps you and thanks for the feedback

Seems like coding in excel is a very powerful tool!

Yes, absolutely It's like flying in air

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) - code runs only when double clicked?

Yes exactly, this is the default worksheet event which will do the given task when double click happens in any of the cell of that worksheet. Refer the attached screenshot to know about remaining available worksheet events.

Worksheet Event.JPG

If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub -- ??

This will run the remaining code when double click happens in A1 cell ALONE, if double click happens in any other cell means then it will end the event

Originally Posted by FDibbins

Thanks for the assist Six

You're Welcome

nudiekap,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

I've just tested the code that Sixthsense provided along with the spreadsheet bos1234 provided in the following thread:

http://www.excelforum.com/excel-gene...condition.html

It works great: the table appears on Sheet1 when G2 matches A2, H2 matches A4, and I2 matches A6.

I'm wondering if the macro can be expanded to test for additional matches that would display different tables? For example, could it display a different table if G4 matches A2, H4 matches A4, and I4 matches A6. That would be perfect for what I'm hoping to achieve, which is basically to be able to pick some values from a few different drop-downs and then get only the table that's appropriate to display for that combination of selected values.

Is this possible?

Thanks in advance for time and help.

ANewton, welcome to the forum

Perhaps you missed post # 21? If so, here is a reminder

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)