adding a hidden numerical value to text entries in a pop-up/ dropdown menu.

Question

Level 1

0 points

adding a hidden numerical value to text entries in a pop-up/ dropdown menu.

I'm trying to create a dropdown menu with text products that have a hidden numerical value. The chart has 7 columns; Client name, Monday:Friday, and total owing. Under the Mon:Fri columns, there are dropdown menus outlining all the potential products a client could purchase (as text). I would like each of these text options to have a hidden price value, so when each in selected for the week, the total owing column tally's up the cost for all services provided in that week.

If that makes sense.....Is this possible?

Mac mini

Posted on Jul 2, 2015 7:58 AM

Reply

Answer 1

t quinn

Level 5

7,042 points

Jul 2, 2015 8:41 AM in response to merewulf

Hi merewulf,

It seems you want to create a lookup table. Keep all your prices there and bring them into your from there.

quinn

Reply

Answer 2

Yellowbox

Level 7

20,329 points

Jul 2, 2015 9:11 AM in response to merewulf

Hi merewolf,

If I understand your aim, you could do this.

Cells B2 to G8 are Pop-Up Menus listing the products, starting with None (Blank).

Then a Price list that can be hidden on another sheet:

Then an intermediate table to look up the prices of chosen products and SUM the total per client. This can also be hidden on another sheet.

Formula in B2 (and Fill Right and Fill Down)

=IF(Table 1::B2="","",INDEX(Price List::$B,MATCH(Table 1::B2,Price List::$A,0)))

That returns a value from the price list that matches the chosen product in Table 1. If Table 1 has a blank cell, it returns "" (blank, NULL).

Formula in G2 (and Fill Down)

=SUM(B2:F2)

Back to Table 1. Formula in G2 (and Fill Down)

=Orders::G2

Please call back with questions.

Regards,

Ian.

Reply

Answer 3

SGIII

Level 8

35,258 points

Jul 2, 2015 12:47 PM in response to Yellowbox

You could also dispense with an intermediate table (at the expense of a longer formula) with something like this:

=VLOOKUP(B2,LUT::$A:$B,2,FALSE)+VLOOKUP(C2,LUT::$A:$B,2,FALSE)+VLOOKUP(D2,LUT::$ A:$B,2,FALSE)+VLOOKUP(E2,LUT::$A:$B,2,FALSE)+VLOOKUP(F2,LUT::$A:$B,2,FALSE)

This won't work with blank cells, hence the - as the first item in the lookup table, and in the popup list:

SG

Reply

Answer 4

merewulf Author

Level 1

0 points

Jul 6, 2015 6:23 PM in response to merewulf

Ok,

This is what I'd like, and I tried copying the formula that you used onto my own table, however the total at the end doesn't add up the weekdays, it simply shows the total of the service listed in 'Monday' cell.

I also don't seem to have $ signs in front of my LUT::A:B - do I have to designate all the cells in the LUT to be currency?

Reply