How to retrieve the value of a dropdown in a formula
Solved
Flow913
Posted messages
7
Status
Membre
-
Flow913 Posted messages 7 Status Membre -
Flow913 Posted messages 7 Status Membre -
Hello everyone!
This is my first message on this forum, thank you in advance for your help and patience!
As much as I'm quite resourceful with Excel and VBA, Word remains a bit of a black mark for me when it comes to "field codes" etc...
I've been tasked with updating a quotation file that calculates a total cost based on a daily rate and the number of days counted in the quote (until now, it was just a formula in a table, a SUM that counts the number of days and multiplies it by the fixed rate... until now).
The update involves accounting for the fact that the rate is no longer fixed, so I need to retrieve this value from a dropdown menu.
I've tried to explain my problem as simply as possible without forgetting too much information.
Do you have any ideas? Or even a solution? :P
Best regards, Florian.
Configuration: Windows 7 / Chrome 40.0.2214.93
This is my first message on this forum, thank you in advance for your help and patience!
As much as I'm quite resourceful with Excel and VBA, Word remains a bit of a black mark for me when it comes to "field codes" etc...
I've been tasked with updating a quotation file that calculates a total cost based on a daily rate and the number of days counted in the quote (until now, it was just a formula in a table, a SUM that counts the number of days and multiplies it by the fixed rate... until now).
The update involves accounting for the fact that the rate is no longer fixed, so I need to retrieve this value from a dropdown menu.
I've tried to explain my problem as simply as possible without forgetting too much information.
Do you have any ideas? Or even a solution? :P
Best regards, Florian.
Configuration: Windows 7 / Chrome 40.0.2214.93
2 réponses
Hello Florian,
to retrieve the value from a dropdown list, in your base formula, which you display with ALT F9, at the place where you want to call the value of the dropdown list, you type CTRL F9 to get { } and inside you indicate the name of your dropdown list.
In the properties of the Number of Days field (numeric field), you must enable the "Calculate on exit" box.
Therefore, you can have your total look like this if B2, B3, and B4 contain the number of days and if Listedéroulante1 is the name of your list:
{ =SUM(B2:B4) * { Listedéroulante1 }}
You lock your form and test it.
If it doesn't work, come back indicating your formula with a concrete example.
Warning: the { } are obtained with CTRL F9. Do not use those from the keyboard, as it would not work.
PS: as you can see, you can indicate the references of the cells as in Excel using A1, etc. You count the columns and rows yourself.
--
C-Claire
to retrieve the value from a dropdown list, in your base formula, which you display with ALT F9, at the place where you want to call the value of the dropdown list, you type CTRL F9 to get { } and inside you indicate the name of your dropdown list.
In the properties of the Number of Days field (numeric field), you must enable the "Calculate on exit" box.
Therefore, you can have your total look like this if B2, B3, and B4 contain the number of days and if Listedéroulante1 is the name of your list:
{ =SUM(B2:B4) * { Listedéroulante1 }}
You lock your form and test it.
If it doesn't work, come back indicating your formula with a concrete example.
Warning: the { } are obtained with CTRL F9. Do not use those from the keyboard, as it would not work.
PS: as you can see, you can indicate the references of the cells as in Excel using A1, etc. You count the columns and rows yourself.
--
C-Claire
So I managed to import the value from the dropdown list into my calculation, which is already a first victory. Apparently, I wasn't using the right type of dropdown list: in the "Developer" tab, I was clicking on the "Drop-down List Box" or "Drop-down List" buttons, when I apparently needed to click on "Legacy Tools" and choose "Drop-down List Box (with ActiveX control)" (otherwise it gives me a syntax error, "Bookmark not defined").
The second problem that arises is that I should not display the value that is involved in the calculation, let me explain:
The dropdown list has several options, each with a corresponding value
- option 1 = price 1
- option 2 = price 2
...
I need my dropdown list to offer the different "option X", and the value "price X" should then be used for the calculation without being displayed.
Is this possible?
You did well to take the list from the inherited tools, but I'm not sure that the list with ActiveX control is useful, unless using macros.
Do you have many options in your dropdown list or is it just 2 or 3?
If there are few, one or two "IF" should be enough.
For example:
{ IF { DropdownList1 } = 1 { =SUM(B2:B4)*100} { =SUM(B2:B4)*200} }
C-Claire
Moreover, my choices are in the form of "CHOICE = 0%", "0% < CHOICE < 2%"… etc.
Should I use quotes? Where should I put the {}?
EDIT: it's too tricky, does anyone know how to write directly via a macro the result in the desired cell (my little Excel methods don't seem to work...). It would be easier to do the nested IFs in VBA ^^'.
Word is not a calculation software, although with some patience, you can make it do a lot of things.
Your formula will look like this for 3 choices:
{ IF { Dropdown1 } = 0% 0% { IF { Dropdown1 } = 2% { =SUM(B2:B4)*100} { IF { Dropdown1 } = 3% { =SUM(B2:B4)*200} } } }
You can easily complete it by copying/pasting the { IF...}
C-Claire