Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-20-2018, 02:36 AM
benji8798 benji8798 is offline Drop down options in Column A, populates column B Windows 7 32bit Drop down options in Column A, populates column B Office 2010 32bit
Novice
Drop down options in Column A, populates column B
 
Join Date: Sep 2018
Posts: 10
benji8798 is on a distinguished road
Default Drop down options in Column A, populates column B

Hi, I have a table with values in Column A



orange
red
blue
yellow

I would like column B to be automatically populated with the $$value. Is this possible in Word. It is for a proposal template, so we want to keep it in the Word format.

Thanks
Reply With Quote
  #2  
Old 09-20-2018, 03:31 AM
gmayor's Avatar
gmayor gmayor is offline Drop down options in Column A, populates column B Windows 10 Drop down options in Column A, populates column B Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

How do the cash values relate to the colours? Reading between the lines do you have a dropdown content control in each cell of column A wth those values?

You are going to have to explain this better if you want help.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 09-20-2018, 03:37 AM
benji8798 benji8798 is offline Drop down options in Column A, populates column B Windows 7 32bit Drop down options in Column A, populates column B Office 2010 32bit
Novice
Drop down options in Column A, populates column B
 
Join Date: Sep 2018
Posts: 10
benji8798 is on a distinguished road
Default

Hi

Column A has a list of values, we will use:

orange
Blue
Red
Yellow

Column B is automatically populated with the $ value based on the selection choosen in Column A:

orange $20
Blue $50
etc etc

This will ensure what the administrator typing up the proposal, if they choose Orange, the cost will automatically populate in next column, so we don't accidentally charge a client for the blue cost if orange is selected.

Does this help?
Reply With Quote
  #4  
Old 09-20-2018, 08:17 PM
gmayor's Avatar
gmayor gmayor is offline Drop down options in Column A, populates column B Windows 10 Drop down options in Column A, populates column B Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

You could use contrent controls to select the values and content control events to update the cash amounts - see attached. Create a new document from the template and select the values in column 1.
Attached Files
File Type: dotm Update Values.dotm (33.7 KB, 18 views)
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #5  
Old 09-20-2018, 08:19 PM
macropod's Avatar
macropod macropod is offline Drop down options in Column A, populates column B Windows 7 64bit Drop down options in Column A, populates column B Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

See also: https://www.msofficeforums.com/word-...html#post47254 for both Content Control Conditional Formatting & Formfield Conditional Formatting
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #6  
Old 09-20-2018, 08:26 PM
benji8798 benji8798 is offline Drop down options in Column A, populates column B Windows 7 32bit Drop down options in Column A, populates column B Office 2010 32bit
Novice
Drop down options in Column A, populates column B
 
Join Date: Sep 2018
Posts: 10
benji8798 is on a distinguished road
Default

Thanks for this. I see the contrent controls where I can add values, but I'm not sure how the content control events updates the cash amounts - are these auto populating? Can you please give any more guidance here?
Reply With Quote
  #7  
Old 09-20-2018, 08:31 PM
macropod's Avatar
macropod macropod is offline Drop down options in Column A, populates column B Windows 7 64bit Drop down options in Column A, populates column B Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Did you select a colour then exit the content control?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 09-20-2018, 08:35 PM
benji8798 benji8798 is offline Drop down options in Column A, populates column B Windows 7 32bit Drop down options in Column A, populates column B Office 2010 32bit
Novice
Drop down options in Column A, populates column B
 
Join Date: Sep 2018
Posts: 10
benji8798 is on a distinguished road
Default

Wow ! That is exactly what I am looking for ! Please can you tell me where I how/where I add the values?

Are there any limitations on how many in the first column, i.e. is there a maximum number of colours I can have?

Thanks
Reply With Quote
  #9  
Old 09-20-2018, 08:55 PM
macropod's Avatar
macropod macropod is offline Drop down options in Column A, populates column B Windows 7 64bit Drop down options in Column A, populates column B Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You could have 1,000 entries if you like, though that's probably a bit excessive... And Word can generate 16,777,216 colours (if you want to add background colouring as per the link I posted).
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #10  
Old 09-20-2018, 08:58 PM
benji8798 benji8798 is offline Drop down options in Column A, populates column B Windows 7 32bit Drop down options in Column A, populates column B Office 2010 32bit
Novice
Drop down options in Column A, populates column B
 
Join Date: Sep 2018
Posts: 10
benji8798 is on a distinguished road
Default

Excessive of 1,000, but this function is exactly what I am looking for. Thanks.


I do not understand how I update the the cash amounts please? Can you please provide further explanation.

Thanks

Last edited by benji8798; 09-20-2018 at 09:55 PM. Reason: more information
Reply With Quote
  #11  
Old 09-20-2018, 11:28 PM
macropod's Avatar
macropod macropod is offline Drop down options in Column A, populates column B Windows 7 64bit Drop down options in Column A, populates column B Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

If you open the template Graham posted and press Alt-F11, that will open the VBA Editor. If you then click on the template's 'ThisDocument' Code module, you'll see the code he's used to do all the work. I'd have approached it differently, along the lines of what you'll see in https://www.msofficeforums.com/word-...html#post46903. There you'll see that the dependent amounts are stored in the value field of each dropdown entry; the code is far simpler that way and gives you a template that's far easier to update as entries are added/deleted and/or the corresponding values change.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #12  
Old 09-21-2018, 03:08 PM
benji8798 benji8798 is offline Drop down options in Column A, populates column B Windows 7 32bit Drop down options in Column A, populates column B Office 2010 32bit
Novice
Drop down options in Column A, populates column B
 
Join Date: Sep 2018
Posts: 10
benji8798 is on a distinguished road
Default

Hi

Thanks for this. I guess I'm still not really understanding how you added the additional details, telephone, email etc. Is this only doable via VBA?
Reply With Quote
  #13  
Old 09-21-2018, 03:26 PM
macropod's Avatar
macropod macropod is offline Drop down options in Column A, populates column B Windows 7 64bit Drop down options in Column A, populates column B Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

If you click on the dropdown and click on Developer|ControlsProperties, then select an entry's display name to edit, you'll see the additional details stored in that item's 'value' field. Similarly, if you click on 'Add' to add another item, a dialog will pop up asking you to supply the display name and value. No VBA is needed to do that setup.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #14  
Old 09-21-2018, 05:00 PM
benji8798 benji8798 is offline Drop down options in Column A, populates column B Windows 7 32bit Drop down options in Column A, populates column B Office 2010 32bit
Novice
Drop down options in Column A, populates column B
 
Join Date: Sep 2018
Posts: 10
benji8798 is on a distinguished road
Default

You are being sooo patient with me. Thankyou.

Hopefully, final question:

I can see where to add the values, I have that bit worked out, but the second box, which you have client details in (i.e. the box with the values in), how do I get that to show?

I have attached a document.

Thanks
Attached Files
File Type: docm Content Controls - questions.docm (38.4 KB, 12 views)

Last edited by benji8798; 09-21-2018 at 05:01 PM. Reason: update
Reply With Quote
  #15  
Old 09-21-2018, 08:20 PM
gmayor's Avatar
gmayor gmayor is offline Drop down options in Column A, populates column B Windows 10 Drop down options in Column A, populates column B Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

It is a bit of a faff to use the values as Paul recommends, as the dialog for inserting content controls is poor; which is why I personally preferred to do it all in VBA, which does however require more code; however what he has done is to separate the 'lines' using a pipe character, then in VBA replace the pipe character with a line break

Thus for Value he has used -

Phone: 08 1111 1111|Fax: 08 2222 2222|Email: sales@roundhousenurseries.com.au

This forum's software may screw that up by applying a hyperlink, but it should give you the idea.

Insert Content Control Add-In may be useful to you, though it does not allow the editing of Values. I may put that in a future version, but as it was written primarily to expedite work for a private client, it is not high on my todo list.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Drop down selection which influences value in an adjacent column lyndap2 Excel 1 08-06-2018 12:08 PM
Formula to check combinations of values in one column to find match from another column kong1802 Excel 1 06-15-2018 05:26 AM
Drop down list to overlay column of cells with different formulae TomJ Excel 0 12-19-2015 08:55 AM
How can I temporarily break a 3 column format in order to type a single column paragraph William P Word 1 01-04-2015 06:40 PM
drop down menu that control filter on other column massi Excel 0 08-24-2010 01:13 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:35 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft