Data Validation - Displaying two columns of results

[TQO23]

Hi All.

I'd like to display two adjacent results from a long list of data by using data validation. My list has two columns which have the name of a racecourse in the UK and then the type of course, e.g "flat" or "jump". So if I have sample data in cells A1:B3

Col A Col B
Aintree Jump
Beverley Flat
Yarmouth Flat
etc etc

I use the Data Validation "Create list from a range" (in Google docs) formula and select the range A1:B3. When I use the drop-down it only displays the first column, ie course name, which is fine but when I select that course I'd also like it to display the course type in an adjacent cell.

Is this possible please and if so how do I achieve this?

Thanks in advance.

Nigel
TQO23

 

[VoG]

You should be able to use VLOOKUP to return the result to the second column http://www.contextures.com/xlFunctions02.html

 

[Aladin Akyurek]

Hi All.

I'd like to display two adjacent results from a long list of data by using data validation. My list has two columns which have the name of a racecourse in the UK and then the type of course, e.g "flat" or "jump". So if I have sample data in cells A1:B3

Col A Col B
Aintree Jump
Beverley Flat
Yarmouth Flat
etc etc

I use the Data Validation "Create list from a range" (in Google docs) formula and select the range A1:B3. When I use the drop-down it only displays the first column, ie course name, which is fine but when I select that course I'd also like it to display the course type in an adjacent cell.

Is this possible please and if so how do I achieve this?

Thanks in advance.

Nigel
TQO23

Name A2:A4 RCOURSE and B2:B4 RTYPE.

Data validate say E2 as List and =RCOURSE as Source.

In F2 enter:

=INDEX(RTYPE,MATCH(E2,RCOURSE,0))

 

[TQO23]

You should be able to use VLOOKUP to return the result to the second column http://www.contextures.com/xlFunctions02.html

Hi Peter and thanks for the prompt response.

I had a look at the link but I've no idea really how to use VLOOKUP combined with data validation. Where would I put the VLOOKUP command, in the adjacent cell to the displayed result?

Could I trouble you to give me an example please based on the short range I quoted?

thanks

Nigel

 

[TQO23]

Name A2:A4 RCOURSE and B2:B4 RTYPE.

Data validate say E2 as List and =RCOURSE as Source.

In F2 enter:

=INDEX(RTYPE,MATCH(E2,RCOURSE,0))

Hi Aladin and thanks for response.

Have set that up but when I try to validate the cell as List and then =RCOURSE it gives me an eror message saying that the range is not valid.

I'm using Google Docs, select Data Validation, Criteria= "Items from a list" "Create list from range" and then I enter =RCOURSE in the shaded "cell range" box. When I try to save it I get a pop-up box saying "Oops The range you specified is not in a valid range format". I've also tried checking and unchecking the "show list of items in a drop-down menu but still with no joy.

I've checked in Data, Manage ranges that the Nickname "RCOURSE" is valid and includes the cell range I need.

Am I missing something?

Thanks again in advance Aladin.

regards

Nigel

 

[Aladin Akyurek]

Hi Aladin and thanks for response.

Have set that up but when I try to validate the cell as List and then =RCOURSE it gives me an eror message saying that the range is not valid.

I'm using Google Docs, select Data Validation, Criteria= "Items from a list" "Create list from range" and then I enter =RCOURSE in the shaded "cell range" box. When I try to save it I get a pop-up box saying "Oops The range you specified is not in a valid range format". I've also tried checking and unchecking the "show list of items in a drop-down menu but still with no joy.

I've checked in Data, Manage ranges that the Nickname "RCOURSE" is valid and includes the cell range I need.

Am I missing something?

Thanks again in advance Aladin.

regards

Nigel

In the drop down menu next to Criteria:, select Items from a list.
Make sure that the button next to the 'Create list from range' option is clicked.
Try now to select A2:A4 (instead of typing =RCOURSE).

Note. The formula is still valid assuming that Google Spreadsheets allows/understands such names.

Or create first the names ranges. Here are the Google rules:

Creating a range name



To create range names, follow these steps:

1. Select the cell or range of cells that you want to define. Or, if you don't know now, you can specify a cell or range later.
2. From the Insert menu, point the mouse to Named ranges.
3. From the drop-down menu, choose Define new range....
4. Enter the range name you'd like to use.
5. If you didn't select the range or cell in step 1, enter it now.
6. Click Save.
7. Click Done.

Here are some guidelines for how to name a range:

• A range name can only contain letters, numbers, and underscores
• It can't start with a number, or the words "true" or "false"
• It can't be evaluated as a range in either A1 or R1C1 syntax
• It must be greater than zero characters, but less than 250 characters
• It can't contain any spaces

 

[TQO23]

In the drop down menu next to Criteria:, select Items from a list.
Make sure that the button next to the 'Create list from range' option is clicked.
Try now to select A2:A4 (instead of typing =RTYPE).

Note. The formula is still valid assuming that Google Spreadsheets allows/understands such names.

Hi Aladin.

Spooky but I tried that just as your response popped up.

All working now and thanks for the responses, very much appreciated.

Nigel

 

[Aladin Akyurek]

Hi Aladin.

Spooky but I tried that just as your response popped up.

All working now and thanks for the responses, very much appreciated.

Nigel

Great. Thanks for providing feedback.