Updating a list of cells based on data entered in another cell

Hello,

I would like to have a two sheet excel workbook. Sheet one has cell A1 with a pulldown list to select option 1,2, or 3. Based on this selection, cell A2 and A3 will update with data unique to 1, 2, or 3. A2 will be a color, A3 will be a number. On sheet 2, the upper left cell of a table will be "1". The cell below it will be the color corresponding to 1, the cell below that will be the number corresponding to 1. The next column of this table on sheet 2 will begin with "2". Below that will be the color corresponding to 2 and so on. How do I make the cells A2 and A3 on Sheet 1 pull this information from sheet 2 based on what was entered in A1? Also, how do I add the pulldown list to include all of the top row options from sheet 2? I would also like to be able to expand the number of options beyond 1,2, and 3 to more numbers, names, etc.

Please help me out! I am pretty good at excel but have limited experience with macros and visual basic.

Thank you!
Mtcellar

Put this formula in A2 of sheet1:

=IFERROR(INDEX(Sheet2!2:2,match(A$1,Sheet2!$1:$1,0)),"")

Then copy it into cell A3 - no need for a macro.

Define a named range covering your numbers in row 1 of Sheet2 (let's call it "values"), then in A1 of Sheet1 you can click on Data Validation, select List in the Allow box, and in the Source box type:

=values

then click OK.

Hope this helps.

Pete

Thank you Pete! Easy solution to follow and worked like a charm!

You're welcome - thanks for feeding back.

If you want to add more columns in Sheet2, ensure you adjust the size of the named range to suit.

Pete