Popup menu list based on condition

Von Bad_Wolf. | 28 August, 2021 - 22:08

Hi all,

This is my problem. I have a "popup menu button" in my spreadsheet. The title is kind of a list based on a condition. This is the list in case there is only 1 item:

meter
piece

This is the list when there is more than 1 item:

meters
pieces

I use an "If" condition inside the "Title" field. The result I get is "meterpiece". I even try to put:
Concat('meter'&Char(13);'piece'&Char(13))
which I thought would give me:

meter
piece

but gives me again meterpiece with a strange rectangle between meter and piece.

Is it possible to get a list of options correctly in the popup title field with a formula?

I thank you in advance for your help.

Chris

Posted in RagTime-Forum | Anmelden oder registrieren um Kommentare einzutragen · Vorheriges Thema · Nächstes Thema »

Re: Popup menu list based on condition

#13071 On 29 August, 2021 07:08 Thomas Kaegi said,

Hello Chris

If I understand your problem correctly I can propose two solutions:

Solution 1:
In a spreadsheet named "Choice" put the following formulas in cells A1:A2:
A1: If(C$1=1;"meter";"meters")
A2: If(C$1=1;"piece";"pieces")
Depending on the contents of cell C1 you will get singular or plural in cells A1:A2.
The formula for the title of your button will have to be: Choice!$A$1:$A$2

Solution 2:
In the spreadsheet "Choice" you don't put any formulas but in cells A1:B2 the values:
A1: meter
A2: piece
B1: meters
B2: pieces
In cell C1 you will again have a value of either 1 or >1.
The formula for the title of your button will have to be: If(Choice!$C$1=1;Choice!$A$1:$A$2;Choice!$B$1:$B$2)

Kind regards, Tom

Anmelden oder registrieren um Kommentare einzutragen

Re: Popup menu list based on condition

#13072 On 29 August, 2021 13:10 Bad_Wolf said,

Hello Tom,

Thank you for your help and information. Your second option was the solution to my problem. I did not know I could do "Choice!$A$1:$A$2". So when your formula succeeded, I experimented a little further.

I created a separate spreadsheet "wsLists" and created a table which contains the following structure:
<measurement> - <Abbr.> - <Conv. Ratio>.
kilogram - kg - 1000
hectogram - kg - 100
decagram - dag - 10
gram - gr - 1
decigram - dg - 0,1
centigram - cg - 0,01
milligram - mg - 0,001
(the decimal separator is the ",' in our decimal system).

So now I can look up the chosen value into the popup menu and in return, the result will be the abbreviation. The popup menu is not visible in the printed layout. Later on, in the calculations, I can convert to the base measurement. All the results can be obtained by combining a simple "SetCell" with a "lookup" function.

Thank you so much for your help because I was completely stuck.

Wish you a nice day and all the best.

Kind Regards,

Chris

Anmelden oder registrieren um Kommentare einzutragen