Forum Discussion

ReggieMitchell's avatar
ReggieMitchell
Copper Contributor
Nov 12, 2023

Prompt User for Input for value in lookup table

=IF(A20=1,VLOOKUP(B20,'Course Offerings'!$A2:$B103,2,FALSE))

 

For the above lookup formula in Excel, I have a 2nd 2-column worksheet. It contains the Course Name in Column 1, and the Course Credits in Column 2. The trouble is that some courses have variable credits instead of a fixed number. How can I insert a place marker in the 2nd worksheet cells that amend the above formula to prompt the user for the variable credit amount?

For example, if an internship course has 1-6 credits for the course in the 2nd worksheet, how do I let the formula for the cell  in the 1st worksheet ask for the specific credits for the course?

excel

1 Reply

Sort By
  • rachel's avatar
    rachel
    Steel Contributor
    Nov 13, 2023

    ReggieMitchell 

     

    Hi,

     

    If you need a Prompt, you probably have to write VBA.

    but if you only need a drop down list,  there is some Excel built-in function you can use. (I attached an example spreadsheet):

     

    e.g. for this "Internship" course, you can input "1,2,3,4,5,6",

     

    Then in your first sheet, you can use "TEXTSPLIT" to create a drop down list:

     

Resources