Numbers Pop-Up Menu & Conditional Statements

[jblaven]

Hi All,

This is my first post and I wanted to introduce myself and say hi. Over the last few years, I've migrated to Apple products and have been very pleased. I've got my 1st generation iPad that has served me well over the last few years, and I'm looking to bring it back into service.

My problem is related to the Pop-Up Menus. I'm beating my head against a wall and not making much progress!

In Numbers, I'm using the Form tabs for data entry into the Sheet tab. I've got some Pop-Up Menus (for product options) that will adjust my final sell price accordingly.

Pop-Up menu contains the following:

• None
• White
• Black
• Blue
• Red

Each color option would have a value associated with it, and would adjust the sell price as needed. I believe I'm having trouble because the options are strings and the functions I've been trying to use are expecting numbers or Boolean values. I guess I can't see the forest for the trees!

Any point in the right direction would be greatly appreciated!

Regards,

Joe

 

[jblaven]

I resolved it. See below for the link to the solution, this is only my 2nd post and I can't put links in my posts until after my 3rd post.

LOL, funny stuff.

Joe

 

[jblaven]

3rd post.

Joe

 

[jblaven]

Alright!!! Post #4!!!

I wanted to follow up and let you know I resolved it. :thumbs: The link below is a good tutorial (using the Mac version of Numbers). Keep in mind that when he types "Grocery" in the formula, he manually places quotes around the word. BUT on Numbers for iPad, don't manually add the quotes, just type Grocery. I guess Numbers for iPad sees the string "Grocery" as being different than the string Grocery.

I also created a separate table on the sheet for the individual color prices.

MacMost Now 557: If Statements and Checkboxes in iWork Numbers

Thanks,

Joe

 

[twerppoet]

Glad you got it figured out.

Thanks for following up with the solution.

Just guessing, because I have not looked at the video yet, but if you enter a string in a formula on the iPad (using the 'abc' key) it automatically adds the quotes in the formula. If you add your own quotes the the inner set of quote marks become part of the string.

 

[mrchin03]

This seems similar to my need.
I

Making a calendar with Daily Meals. Variables are the various food items available for Breakfast, Lunch, Dinner and 3 snacks. Each meal for each day of the week has the same items to choose from. Each meal has a calorie and carb amount associated with it in my other table.

How can I get a total for each (cal and carbs), to add up, based on the Pop-up menu item chosen per meal. It would reference the 2nd table, which could be invisible or on another sheet.

 

[twerppoet]

This was a fun challenge, so I made a 'much' simpler spreadsheet to play with. Here are my comments.

1) The function that seems to fit best is the VLOOKUP function, found in the Reference section of functions. If you tap the fx icon when editing a function you can look through the library. Tapping on the (i) icon next to the function will get you a description and examples to look at. If you encapsulate the VLOOKUP in an IFERROR fucntion you can prevent the error icon that shows up in the cell when there is no match. This is shown in the bottom of my screenshot.

2) While you could use the VLOOKUP function in one humongous equation to sum the entire row, that would be crazy. Better is to create a couple of extra columns for each day, one for carbs and one for cals. You can then use SUM on these columns to get your totals. If you find that too cluttered, you can hide those columns once you are sure they work. Hidden columns can still be referenced in functions. You'll just need to unhide them to edit or make changes.

3) The Pop-up menu works pretty well for the short list if items you are showing in your spreadsheet, however if you expand on choices for each meal, this lists are likely to get unusably long. The way I'm using the VLOOKUP function allows for close matches. In theory this would make it possible to simply type in the name of the food item. I don't know how well this would work in practice, and did not give it a try.

Also, I cant find a way to populate the Pop-UP menu from the table, so you are stuck with manually creating each one. Using exact match would mean any mistakes will break the spreadsheet.

If you want a download link to my test spreadsheet, let me know. I think my Dropbox account still works. Been a year or two since I used it for anything.

Edit: Adding link to Dropbox file just in case you, or anyone else is interested. May disappear when/if I drop Dropbox.

Dropbox

www.dropbox.com

 

[mrchin03]

This was a fun challenge, so I made a 'much' simpler spreadsheet to play with. Here are my comments.

1) The function that seems to fit best is the VLOOKUP function, found in the Reference section of functions. If you tap the fx icon when editing a function you can look through the library. Tapping on the (i) icon next to the function will get you a description and examples to look at. If you encapsulate the VLOOKUP in an IFERROR fucntion you can prevent the error icon that shows up in the cell when there is no match. This is shown in the bottom of my screenshot.

2) While you could use the VLOOKUP function in one humongous equation to sum the entire row, that would be crazy. Better is to create a couple of extra columns for each day, one for carbs and one for cals. You can then use SUM on these columns to get your totals. If you find that too cluttered, you can hide those columns once you are sure they work. Hidden columns can still be referenced in functions. You'll just need to unhide them to edit or make changes.

3) The Pop-up menu works pretty well for the short list if items you are showing in your spreadsheet, however if you expand on choices for each meal, this lists are likely to get unusably long. The way I'm using the VLOOKUP function allows for close matches. In theory this would make it possible to simply type in the name of the food item. I don't know how well this would work in practice, and did not give it a try.

Also, I cant find a way to populate the Pop-UP menu from the table, so you are stuck with manually creating each one. Using exact match would mean any mistakes will break the spreadsheet.

If you want a download link to my test spreadsheet, let me know. I think my Dropbox account still works. Been a year or two since I used it for anything.

Edit: Adding link to Dropbox file just in case you, or anyone else is interested. May disappear when/if I drop Dropbox.

Dropbox

www.dropbox.com

Awesome. Had to figure some stuff out. But all worked out. Odd that my calendar table was acting up, but that's maybe because I started with the Calendar Template. So I made new columns for the number tally but could only hide the first 2 for Sunday. All days after were grayed out. So I just ended up making separate tables for the calculations on a separate sheet.

Thought I'd share my results so you can see where you fun finished. Thanks for your help.

FoodSchedule GoogleDrive

 

[twerppoet]

You're welcome.

I like your spreadsheet. It looks great.