Need an "If" Function that will result in a list of items dependent on a reference number

[akrondan1]

Hi All!

Back again with a problem, but I always seem to get the help I need here.

I am making a "flowchart" to make it easier for our event planners to select menu options for their groups. I already have the sheet set up to produce reference numbers based on facility, room, and number of guests. What I want the formula to do is reference a list of menu options depending on what reference number is in the cell, and then list those options in the column.

=IF(F5=2,List2,(IF(F5=6,Blair2,(IF(F5=10,Blair3,(IF(F5=14,Blair4,(IF(F5=18,Blair5,(IF(F5=(4),Garden,FALSE)))))))))))

I THINK the formula is sound, but it wont pull the list to the main sheet from the reference sheet. It returns a #VALUE error.

Thanks!

[ChemistB]

Assuming your lists are named properly, try this

=IF(F5=2,INDEX(List2, ROWS($A$1:A1)),(IF(F5=6,INDEX(Blair2, ROWS($A$1:A1)),(IF(F5=10,INDEX(Blair3, ROWS($A$1:A1)),(IF(F5=14,INDEX(Blair4, ROWS($A$1:A1)),(IF(F5=18,Blair5,(IF(F5=(4),INDEX(Garden, ROWS($A$1:A1)),FALSE)))))))))))

dragged down. Is that what you are looking for?

[akrondan1]

So Close!

That pulls the first cell in column of the list. I need it to pull the entire list. (some up to 150 cells)

[ChemistB]

If you drag the formula down, it will pull the other 149 values. Do you want to pull them all into one cell? If so, you'll probably want VBA.

[akrondan1]

Works Great Thanks!