looking for some Excel help - drop downs and multiple cell population

SGWills

SGWills

SGWills

Associate
Joined
24 Jul 2007
Posts
1,894
Location
Swansea, Wales
so I'm after some info on the best way to set up a spreadsheet.

The idea behind it is that you will have a drop down with a limited selection of options (between 3 - 5) upon selecting one or more of the options, it would self populate a list of questions.


in theory it would be something such "lost item" to be selected from the drop down, then a series of questions (when was it purchased, original price etc) would be generated either within excel or mail merged in to word.

Firstly is this something that can be done? if so, whats the best way to go about it

Thanks in advance
 
It certainly is possible in Excel, whether this is the best way to do it I don't know but it is one way.

For each set of questions, use 2 columns. In the first simple put ascending numbers (i.e. 1, 2, 3, 4, 5..) In the second column type the relevant question. For example;

LostData
1 What?
2 When?
3 How?
4 Why?
5 Where?

LostItem
1 Value?
2 Cost?
3 Time?
4 Lost?
5 Found?

You can create as many lists as you need.

Highlight each table of numbers and questions and give each range of cells a relevant name (I called them the same as the headings; 'LostData' and 'LostItems')

Create a separate list of all the names you gave the tables of questions.

Then create a separate table, At the top using data validation create a drop down list of the names of the tables of questions. Underneath, in separate cells type your list of numbers 1,2,3,4,5... etc. Next to number 1 type;

=IFERROR(VLOOKUP(A2,Indirect($B$1),2,FALSE),"")

Where a2 is the number 1 representing question 1
B1 is the drop down list of the table names
Indirect substitutes the name of the table in to the vlookup formula so it looks up the relevant question number 1, 2, 3, 4, 5, etc.
The IFERROR simply blanks the questions if no question list is selected (or you have varying numbers of questions)

Drag this formula down as far as you need.

Not the easiest to explain so uploaded the example above: https://www.dropbox.com/s/9isy1akvxm8pds9/OCUK Excel lists.xlsx?dl=0

Changing the drop down menu in B2 will change the list of questions underneath to what you set up in your earlier tables.
 
Last edited:
Happy to help :)

@SGWills I realised you said in the OP that there would varying number of questions so messed around a little more with the spreadsheet and updated the file on dropbox. I left lostdata as 5 questions but shortened lostitem to only 3 but you can use the same basic formula to vlookup the question numbers too. This way, you only get the numbers appear for the relevant number of questions.

Worth noting in this, column A is hidden and has to have all the numbers in up to the maximum number of questions (Lostdata is the longest with 5 questions, so it counts to 5. If you have one with 7, then column A would need to count down to 7)

My original post covers the bit you need, this is optional really just to keep things neater.
 
makes sense. The idea behind it is for newer starters within work, they select what they are dealing with and then it will populate with questions they will need to ask.
It would be subject to change so ideally it will be a quick amendment if required to add in an extra question or remove one :)
 
SGWills said:
makes sense. The idea behind it is for newer starters within work, they select what they are dealing with and then it will populate with questions they will need to ask.
It would be subject to change so ideally it will be a quick amendment if required to add in an extra question or remove one :)
Click to expand...

If you want to make it easy to expand, you could extend the tables of questions down as far as you like. For example, LostData could be;

LostData
1 1 What?
2 2 When?
3 3 How?
4 4 Why?
5 5 Where?
6
7
8
9
10

In the blank cells next to the unused questions (6, 7, 8, 9 and 10) you'll probably need to put ="" in them so the vlookup doesn't return a 0 in the main question table. Extend column A down to 10 too. If you wanted to add a sixth question, it would be a simple job of typing '6' in the middle column and the question in the right most column of that table and it would automatically appear in the main question table selected using the drop down menu.
 
@Greboth just so I can check as well, with the drop down options, would it be a case if they select Option A, it would populate all the questions, 1 to 6 for example, that is under that table that was selected from the drop down?
 
SGWills said:
@Greboth just so I can check as well, with the drop down options, would it be a case if they select Option A, it would populate all the questions, 1 to 6 for example, that is under that table that was selected from the drop down?
Click to expand...

Correct :)

It works like a regular vlookup formula by looking up each value in column A and returning the question number and relevant questions. The only variance is the indirect table reference which means you can get the vlookup to look at different tables depending on the drop down selection.

I don't think it's the easiest thing to explain without looking at the spreadsheet. I presume you're at work so maybe can't access the drop box link. If so, if you want to trust me your email address I can email a copy to you :)
 
its a mixture of things lol. in work reviewing at the moment and I been asked to do it as a sideline thing. I will jump in to the drop link when I finish to get it downloaded and have a run through it :)

If I get a bonus for it I will send you a percentage ha!

I will let you know how I get on with it
 
No worries, just quote me or @ me so I get the notification if you have any other questions / don't understand something.

I've updated the link with the update of post 7 too.

Edit: Well guess I need to look up how to do a pivot and slicer now then :)

Edit 2: @james.miller that's quite handy to know. I never knew you could do that with pivot tables.
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom