List

Z

zinah

Active Member
Joined
Nov 28, 2018
Messages
355
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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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
163
Alex Blakenburg
A
D
  • Solved
Join multiple rows values in same cell, based on 2 conditions.
Replies
5
Views
575
etaf
etaf
E
  • Question
VBA Copying Set of Data n Times
Replies
5
Views
421
Fluff
Fluff
Z
  • Solved
Filter Function with Multiple Criteria
Replies
8
Views
429
JvdV
J
E
  • Question
Simple Formulas to list ALL Next payments due (Closest Date to Today) from a list
Replies
0
Views
535
EmadMassoud
E

Forum statistics

Threads
1,215,646
Messages
6,125,997
Members
449,279
Latest member
Faraz5023

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