none
creating a user form that can incorporate the use of excel formulas RRS feed

  • Question

  • I have created in Excel 2010 a maintenance planner that incorporates quite a lot of formulas so I can see the number of visits due per year, when due by month, and a box that shows if it is due or completed, or been carried out late, or classed as carried out.

    As I have no experience with excel formulas it does tend to take me a while to complete each line of information when entering a new maintenance client.

    Sometimes I need to enter around 800 clients which can and does take me weeks to do. So, what I want to do is to create a simple user form where each time I want to enter a new client I put the details such as client name, number of visits per year, discipline, months due, and it will ever the details direct to the planner.

    I know this sounds simple enough on the face of it but I also have to make sure the respective formulas are also copied automatically into each line so the planner continues to operate correctly.

    If you can help at all it would be greatly appreciated.

    Many thanks 

    Saturday, February 13, 2016 9:38 PM

Answers

All replies

  • RE:  Entering data

    You should be able to enter one formula in a cell and then fill that formula down the column...



    '---
    Jim Cone
    Portland, Oregon USA



    Sunday, February 14, 2016 2:35 AM
  • Not to answer your question but something that you may find useful:

    If you are frequently adding in the same few options into a column it may be worth using a drop down box. To make one of these you should:

    - Make a list of the options you want on the dropdown (On the same sheet or on another sheet in the workbook <-- second option is usually prefered)

    - Select the a cell in the column you want the drop down to be in.

    - Goto the Data tab and click on Data Validation.

    - Allow: List

    - Source:Click on the icon on the right so you can select your range.

    (Hold F4 when selecting the list you made before to make it absolute or add $ signs before each number and letter. Example: =$C$11:$C$14)

    -Click ok.

    This should now give you a dropdown in your selected cell.

    when the dropdown is blank, grab the fill handle and you can drag it up, down, left or right to apply the drop down box to other cells easily.

    I hope this helps.

    Happy Excelling!

    Monday, February 15, 2016 9:40 AM