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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
293
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
293
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,122,979
Messages
5,599,156
Members
414,294
Latest member
shariflotfi

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
Top