Drop Down's and Formulas

Question

Drop Down's and Formulas

Hello All,

I'm having some troubles and was hoping for your help.

What i'm trying to do is -

I want a drop Down list to be something like names, but if it equals said name then i want it to change the cell next to it to have the corresponding amount associated with that name. for example

Names are -

Steve

Cameron

Lauren

if it equals Steve i want the cell next to it to equal with $2

if it equals Cameron i want the cell next to it to equal $2.50

if it equals Lauren i want the cell next to it to equal $3.00

Is this possible? if not is there a workaround with the formulas to make something like this possible?

Mac Pro (2023)

Posted on Jul 20, 2023 9:17 PM

Reply

Answer 1

Top-ranking reply

Barry

Level 9

60,384 points

Jul 21, 2023 12:21 AM in response to NumbersMan123

What you are looking for is a Pop Up Menu listing the names, a two column list with the names in one column and the corresponding amount in the column to its right, and a formula to return the correct amount, depending on which of the names is chosen.

Column B of Table 1 is formatted as a Pop Up menu cell, with the three names entered as Items 1, 2 and 3 of the menu. Clicking the Start with Blank adds the None item to the top of the menu, with the result shown in cells B2 and B6.

The XLOOKUP formula shown below Table 1 is entered as shown in cell C2 of that table, then filled down to cell C6

Table 2 contains a list of the names included in the pop up menu in column A, and the money amounts corresponding with each name in column B.

Clicking one of the cells in column B selects the cell, and displays the tabopens the menu for that cell, and displays the tag indicating the cell contains a menu.

Clicking the tag opens the menu, then clicking a name inserts that name in the cell containing the list…

…and closes the menu.

The formula in the same row of Column C gets the name from the cell in column B, searches for it in column A of Table 2, finds it, and returns the amount in the same row of column B of that table.

Regards,

Barry

Reply

Answer 2

SGIII

Level 8

38,032 points

Jul 21, 2023 8:08 AM in response to NumbersMan123

Use the SWITCH function. It is very simple and compact:

In the example I put the Pop-Up Menu in A2.

The formula in the cell next to it, B2, is:

=SWITCH(A2,"Steve",2,"Cameron",2.5,"Lauren",3,"")

As you can see in the screenshot I formatted that cell as Currency.

Drop Down's and Formulas

Similar questions