Q: Problem with Formula - multiple outcome IF then statement

Thomas,

With regard to:

IF B2 = Microphones, then display CELL Contents A1from PRODUCTS spreadsheet.

IF B2 = MIXERS, then display CELL Contents A2 from PRODUCTS spreadsheet

IF B2 = RECORDERS, then display CELL Contents A3 from PRODUCTS spreadsheet

 

 

Can you clarify what you mean by the words "PRODUCT spreadsheet"?

A) do you mean a table in the same sheet of the same document?

B) do you mean a table in a different sheet of the same document?

C) do you mean a table in a different document?

If you mean C then there is a problem addressed only by consolidating the information into the same document.  If A or B then all is good

Assuming A or B:

A1=IF(B2="Microphones", PRODUCTS::A1, IF(B2="Recorders",PRODUCTS::A2,IF(B2="Mixers",PRODUCTS::A3,"")))

this is shorthand for .... select cell A1 in the the top table, then type (or copy and paste from here) the formula:

=IF(B2="Microphones", PRODUCTS::A1, IF(B2="Recorders",PRODUCTS::A2,IF(B2="Mixers",PRODUCTS::A3,"")))

Hi Thomas,

It seems you are asking a variation on the conditional popup question.

I have been playing with this idea of conditional pop-ups. This is a variation of an answer I gave here:

Re: changing Pop-up-Menu

It is much simpler (Yea!).

This would all happen in your checklist table. Each of your current rows there would have 3 additional rows under it that would be hidden or revealed using filters. For instance Select Recorders category and Choose Recorder popup is revealed.

B2= IF(A1="Microphones","show","")

B3= IF(A1="Mixers","show","")

B4= IF(A1="Recorders","show","")

Column B can be hidden.  The choice made in A1 determines which of A2, A3 or A4 can be seen.

Does this seem useful?

quinn

Hi again Thomas,

I didn't reply to the second part of your question, which is the self updating popup request. Numbers 3 does not have dynamic updating of popups. we have to do it manually. I would have a table with a column for each of your product popups.

Once I choose Pop-Up Menu all 4 entries are popups. To add a new product I type it in and format it with the originals.

The trickiness will ensue when you want to add the new popup to your checklist table. It is not even that tricky.

You have to trash your filter on column B temporarily and filter column A for your soon to be altered popup. Say the new popup is for a recorder. Your filter on A will be:

This will only find rows where a recorder has not already been chosen. Select Column A and paste your new popup in.

Then delete your filter on column A and redo the "show" filter on column B.

When inspired, go to Numbers feedback and request Dynamic Pop-Ups!

Hope this isn't too much

quinn

Hi Thomas,

Following on from quinn's reply. Here is a way to make updates semi-automatic. Here is the table before applying quinn's idea for a filter on "show".

Choose a category. Formula in C2 (and Fill Down)

=IF(B$1=A2,"Show","")

This formula will cope with new categories.

Now apply the Filter. Note that when B1 is 'Choose a category' none of C contain show, so all Body Rows are hidden.

Now for the update trick. The formula in C does not need revising.

Add another category (complete with the sub-category Pop-Up)

and edit the 'Choose a category' Pop-Up in B1 to include Geegaws. Formula in C5 will insert Show when Geegaws are chosen.

Because Row 1 is a Header Row, the filter does not affect it.

We are getting there, quinn!

Regards,

Ian.