What's new
Apple iPad Forum 🍎

Welcome to the Apple iPad Forum, your one stop source for all things iPad. Register a free account today to become a member! Once signed in, you'll be able to participate on this site by adding your own topics and posts, as well as connect with other members through your own private inbox!

Numbers Pop-Up Menu & Conditional Statements

jblaven

iPF Noob
Joined
Jan 9, 2013
Messages
4
Reaction score
1
Location
Georgia, USA
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! :confused:

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! :D

Any point in the right direction would be greatly appreciated!

Regards,

Joe
 
OP
J

jblaven

iPF Noob
Joined
Jan 9, 2013
Messages
4
Reaction score
1
Location
Georgia, USA
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
 
Last edited:
OP
J

jblaven

iPF Noob
Joined
Jan 9, 2013
Messages
4
Reaction score
1
Location
Georgia, USA
Alright!!! Post #4!!! :D

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

iPad Fan
Joined
Jan 8, 2011
Messages
24,150
Reaction score
15,398
Location
Milton-Freewater, OR
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

iPF Noob
Joined
Feb 25, 2024
Messages
2
Reaction score
2
This seems similar to my need.
I
Screen Shot 2024-02-25 at 2.38.04 PM.png
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

iPad Fan
Joined
Jan 8, 2011
Messages
24,150
Reaction score
15,398
Location
Milton-Freewater, OR
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.

 

Attachments

  • IMG_0183.jpeg
    IMG_0183.jpeg
    166.6 KB · Views: 5
Last edited:

mrchin03

iPF Noob
Joined
Feb 25, 2024
Messages
2
Reaction score
2
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.

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
 

Most reactions

Latest posts

Top