Numbers - Adding Sub Pop Up Windows

Hi Marie,

I'll repeat here what I noted when I returned the file to you.

I found the file to be slow in updating when values were changed, which seemed odd since it wasn't very large. I made a few changes to streamline the order table, but not to change the functionality, as it was working properly as far as I could determine.

• Added a Footer Row for the totals
• Eliminated hidden columns by using the SUMPRODUCT function in the subtotals.
• Having added the Footer Row, I was able to assure that all body rows were identical in programming, thus the table can now be expanded by tugging on the Add Rows handle and the formulas will automatically appear in the new rows. Now you can work with a small table by default and only enlarge it when needed.
• Used the shortcut notation for ranges that include all rows in the body. =SUM(G) rather than SUM(G3:G10) for example. (I don't recall if that was an actual use)
• Changed from Stepper to Slider, just to see if the speed changed with table streamlining. It must have because the Slider worked great. See if you prefer it -- it's not for everyone, but I like it.
• Minor formatting changes

Regards,

Jerry

Marie,

Thanks for the example data. Here's a solution using a Slider. It could be that a Stepper would be preferred. It just depends on how many selections you have. A stepper with more than a few dozen numbers in its range can be a bit touchy. You need to experiment.

Here's my solution.

Note that the first Popup item is blank (a space) so that the user must make a selection to see a result.

Note that the slider begins at zero, also an out of range selection, requiring the user to make a selection. This is a matter of preference. You may want to default the starting number to a particular item.

The expression in the Result column is:

=IFERROR(OFFSET(LookupTable :: $A$1,B,MATCH(A,LookupTable :: $1:$1, 0)-1), ".")

Note that the invalid selection (preset) result is a period.

Regards,

Jerry

Hi Marie,

The formula below is based on the original version of the document I received, not including Jerry's revisions.

Cell F1 contained the words "SHOW PRICE"

I replaced that with a Pop-up menu cell containing three items:

Note that in each of these the first word (and the second word in the last item) is followed by an option-return character to force a new line. The three entries contain no spaces.

With the words in ALL CAPS, and the small amount of space between lines, the full menu is a bit difficult to read. I would suggest separating them by including an option-return after each wird in the first two items, and after each of the first two words in the third item. The resulting menu list will have this appearance:

RETAIL

PRICE

SHOW

PRICE

BUYER

GROUP

PRICE

On the PART NO./PRICING table, cells C1, D1 and E1 contain the same items. Note that the entries must match exactly with those in the pop-up menu, including no spaces, and the option-returns inserted in the menu items.

The formula, entered in F2, and filled down the rest of column F, used VLOOKUP to retrieve the prices, and MATCH to determine the column from which to retrieve them.

Show Price Sheet::F2 (and filled down):

=VLOOKUP(A,'PART NO./PRICING' :: A:E,MATCH($F$1,'PART NO./PRICING' :: $1:$1,0),0)

Regards,

Barry

PS: Thanks for sending the original sheet. I hadn't replied earlier as I wasn't able to get to the file until after Jerry's post. He's pretty much covered all the changes I would have suggested (and more). 🙂

Marie,

The same question was asked last week. We often suggest that for the second pop-up you use a Slider or Stepper to set an index number that points into a lookup table to a range determined by the first, actual, pop-up.

I did just think of another approach, but I don't think many would find it very charming. You could have two secondary pop-ups and direct the user's attention to one or the other, and have the program take only the data from the appropriate one. Or, you could use an overlay table to obscure one or the other of the secondary pop-ups.

All rather clumsy workarounds, but doable.

Jerry

Marie,

I understand your disappointment, but Numbers isn't Excel. It's a much simpler spreadsheet program that is targeted at simplifying the more common uses at the expense of more complex operations. Also, Numbers is only in it's second major version, so it may gain features in it's next release. "Huge oversight" or "design choice" is a matter of perspective here, I believe.

After I made the switch to Mac about seven years ago I purchased Office for Mac because Numbers didn't exist yet. By the time Numbers was introduced, I was thrilled that it could at least add, subtract, multiply and divide, which was all I really needed to satisfy my simple spreadsheet needs at the time. The quantity of supported functions in Numbers reoughly doubled from version 1 to version 2 and now covers the basics and much more.

Numbers remains focused on the printed or displayed end result, as in report graphics. Tools for the user have taken a back seat, as I see it. Where Numbers is really weak is in producing documents to be edited or used for data entry by others. You can see this especially in the lack of tools for protecting cells and for data validation, and in your particular question.

If you'd like, I can help you with one of the options I described.

Jerry

Marie,

Delete the IFERROR function. Your expression will look like:

=OFFSET(LookUp Table :: $A$1,C,MATCH(B2,LookUp Table :: A$1,0)-1)

Then read the error message where the triangle appears in the Description column to see what the matter is.

A likely problem is that the category names in the header do not exactly match the category names in the Popup menu. I always use Copy/Paste for filling in the menu, from the header entry, so there is no possibility of a difference. Then too, it could be something else, but the easiest way to proceed is to let the program tell you what is wrong.

Jerry

Hi Marie,

This looks to be the source of the error:

MATCH(B2,LookUp Table :: A$1,0)-1)

A$1 is a reference to a single cell. MATCH can find "Archery Tools" in that single cell, but can't find the other categories unless it has a range of cells to search:

MATCH(B2,LookUp Table :: A$1:C$1,0)-1)

which specifies a three -cell range, OR

MATCH(B2,LookUp Table :: $1:$1,0)-1)

whiich specifies all of row 1 as the range.

Regards,

Barry

Marie,

I'm curious about the Part Number in your screen shot. Is that an entry or a fetched value? Lookup table could be arranged differently if you need to retrieve that number along with the description.

Jerry

Doesn't sound like you are collecting and storing the information getting entered, but if you are indeed collecting large amounts of data at these trade shows, please backup frequently and don't let the file get too large (I don't know what too large is, but occasionally folks post here complaining files stop opening on the iPad).

PineRidgeMarie wrote:

... Is there a way I can send you the entire spread sheet file and you can see how I have it organized.

Marie,

Feel free to email it to the address in my profile. Control-Click the filename in Finder and select Compress (filename). Then send the .zip that results.

Jerry

"Is there a way I can send you the entire spread sheet file and you can see how I have it organized."

Hi Marie,

I'd like to see it as well. Address available in my profile. Other instructions are the same as Jerry's.

Regards,

Barry