List

Z

zinah

Active Member
Joined
Nov 28, 2018
Messages
368
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a table that has list of questions and answers, the answers depend on county. What I need is to create a dynamic list where it can take the answer based on the country I choose, just like the table showing below:

The questions list is the same for all countries, but what I need if I change the cell "Country" which has drop-down list and choose specific country, the answers change based on question list. what is the best formula that can list the answers?
Answers by country
QuestionsCountry1Country2Country3Country4Country5Country6Country7Country8Country9Country10
AAABC1BC1ABC1ABC1ABC1ABC1ABC1ABC1ABC1ABC1Country
BBABC2BC2ABC2ABC2ABC2ABC2ABC2ABC2ABC2ABC2QuestionsAnswers
CCABC3BC3ABC3ABC3ABC3ABC3ABC3ABC3ABC3ABC3AA
DDABC4ABC4ABC4ABC4ABC4ABC4ABC4ABC4ABC4ABC4BB
EEABC1ABC1ABC1ABC1ABC1ABC1ABC1ABC1ABC1ABC1CC
FFABC2ABC2ABC2ABC2ABC2ABC2ABC2ABC2ABC2ABC2DD
GGABC3ABC3ABC3ABC3ABC3ABC3ABC3ABC3ABC3ABC3EE
HHABC4ABC4ABC4ABC4ABC4ABC4ABC4ABC4ABC4ABC4FF
IIABC1ABC1ABC1ABC1ABC1ABC1ABC1ABC1ABC1ABC1GG
KKABC2ABC2ABC2ABC2ABC2ABC2ABC2ABC2ABC2ABC2HH
LLABC3ABC3ABC3ABC3ABC3ABC3ABC3ABC3ABC3ABC3II
MMABC4ABC4ABC4ABC4ABC4ABC4ABC4ABC4ABC4ABC4KK
NNABC1ABC1ABC1ABC1ABC1ABC1ABC1ABC1ABC1ABC1LL
MM
NN
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
One way is to make a Named range for each of your countries. Easiest way to do that, given that layout, would be to select the B2:K15 range, go to the Formulas tab and in the Defined Names section, click on Create from Selection. Then select P5:P15, and enter this formula:

=INDIRECT(P3)

and confirm with Control+Shift+Enter. P3 has the name of your country.
 
Upvote 0
Thank you soooo much, that was very informative way to resolve my issue.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K0st4din
  • Solved
Macro hide inconsistent selected columns in selected worksheets
Replies
6
Views
358
Alex Blakenburg
A
D
  • Solved
Join multiple rows values in same cell, based on 2 conditions.
Replies
5
Views
733
etaf
etaf
E
  • Question Question
VBA Copying Set of Data n Times
Replies
5
Views
557
Fluff
Fluff
Z
  • Solved
Filter Function with Multiple Criteria
Replies
8
Views
557
JvdV
J
E
  • Question Question
Simple Formulas to list ALL Next payments due (Closest Date to Today) from a list
Replies
0
Views
637
EmadMassoud
E

Forum statistics

Threads
1,223,879
Messages
6,175,150
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top