Data Validation Drop Down List w/Multiple Columns w/o Combo Box

jegarner

New Member
Joined
Nov 16, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
I have Sheet1 and Sheet2 tabs in my excel spreadsheet.

Sheet1 has data in columns A & B.
Sheet1 column-A1 has a column header named Food
Sheet1 column-B1 has a column header named Weight

There are ten items under the headers in each column (Sheet1 A2:A11,B2:B11).

I'd like to create a data validation list drop box Sheet2 A1. When I click on Sheet2 A1, I'd like to see the data list showing both columns in Sheet1 (A1& B1), and have it populate Sheet2 A1&B1 with the data selected.

Can this be done only in Data Validation and without Combo Boxes?
I'm open to any suggestions making it as easy as possible.

Thank you in advance!
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
250
Office Version
  1. 2007
Platform
  1. Windows
In the Sheet2 next to the cell A1, or some unusable location connect cells Sheet1!A1:A1 & Sheet1!B1:B1.
Copy this formula for all rows.
Then in the data validation set this new range in the Sheet2 as data validation list source.
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
250
Office Version
  1. 2007
Platform
  1. Windows
Sorry I mean: "In the Sheet2 next to the cell A1, or some unusable location connect cells Sheet1!A1 & Sheet1!B1."
 

jegarner

New Member
Joined
Nov 16, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Sorry I mean: "In the Sheet2 next to the cell A1, or some unusable location connect cells Sheet1!A1 & Sheet1!B1."
Thanks for the suggestion but perhaps I'm using your instructions incorrectly.
On Sheet2 in column D1 I inserted Sheet1!A1 and E1 I inserted Sheet1!B1 (I also tried it with an = sign before each formula.
Then I selected Sheet2 A1, Data Validation, and choose the range of Sheet2!D1:E1 just to test. It only populates Sheet2 A1, while I'm trying to populate Sheet2 A1 & B1 with a single selection from the drop down list.
What am I doing incorrectly here?
 

jegarner

New Member
Joined
Nov 16, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
I also inserted =Sheet1!A1:Sheet1!B1 into Sheet2 D1 which populates Sheet2 D1 and E1 correctly with data from Sheet1,
but using either Sheet2 D1 or Sheet2 D1 & E1 in the Data Validation range still only gets me a single item to select from the drop
down list in Sheet2 A1.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,011
Messages
5,575,541
Members
412,676
Latest member
Otterbox
Top