+ Reply to Thread
Results 1 to 7 of 7

Want to make a data entry tool which creates a chart, don't know how to make it flexible.

  1. #1
    Registered User
    Join Date
    05-09-2022
    Location
    UK
    MS-Off Ver
    MS Office 2016
    Posts
    15

    Want to make a data entry tool which creates a chart, don't know how to make it flexible.

    Hi Folks.

    I am currently trying to create a user-friendly 'dashboard', where a user can enter data (category names and values) and a 100% stacked area chart will be created. This is fairly simple in principal, but there are complications.

    The X axis of this chart is static - it is time in monthly increments from present day to 5 years into the future. I want the user to be able to enter their own categories (whatever names they want) and values (percentages, adding to 100), but also to choose the dates within the 5 year period that the chart will call upon for their inputted values. The full 5 years needs to be displayed regardless. Is there a way to build such a dynamic chart creation tool - where the points of the x axis are able to be changed without causing issues, or leading to the chat X axis changing with it.

    Any help is much appreciated.

    Thank you,

    Jack.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Want to make a data entry tool which creates a chart, don't know how to make it flexib

    Hi,

    If you'd like to submit an example of the chart you want and a set of current data then no doubt we can add the stuff you need to parameterise it.

    It may involve macros. Is that OK and is there a maximum number of categories? Bear in mind that charts can get very 'busy' and become unreadable if you attempt to show too much
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    05-09-2022
    Location
    UK
    MS-Off Ver
    MS Office 2016
    Posts
    15

    Re: Want to make a data entry tool which creates a chart, don't know how to make it flexib

    Hi Richard, thanks for your reply!

    I've attached a rough example of the chart below. It currently has static points that people can input their percentages, and no ability to easily rename categories nor to easily change the editable dates.

    Macros would be fine yes, I have some limited experience with them and I am happy to use them as long as they can appear simple to a user of the tool.

    As a maximum number of categories, it may be up to 10. This is a lot, the current example has only 3. Please let me know if you think this would be an issue.

    Thanks again,

    Best,

    Jack
    Attached Files Attached Files

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Want to make a data entry tool which creates a chart, don't know how to make it flexib

    How are the final targets in B40:B42 reflected in the chart? I presume these are manually added

    Is 2016 your latest Excel release or do you have Excel 365?

  5. #5
    Registered User
    Join Date
    05-09-2022
    Location
    UK
    MS-Off Ver
    MS Office 2016
    Posts
    15

    Re: Want to make a data entry tool which creates a chart, don't know how to make it flexib

    Yes they are manually added and picked up by the chart. Them being manually added is fine, as long as they are picked up by the chart like it is in this example.

    2016 is the only release I am able to work on I'm afraid.

    Cheers

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Want to make a data entry tool which creates a chart, don't know how to make it flexib

    Is there a maximum number of mid month target months you want to chart? Your example has 3 but in extremis could it be all 60 months?

  7. #7
    Registered User
    Join Date
    05-09-2022
    Location
    UK
    MS-Off Ver
    MS Office 2016
    Posts
    15
    Quote Originally Posted by Richard Buttrey View Post
    Is there a maximum number of mid month target months you want to chart? Your example has 3 but in extremis could it be all 60 months?

    In theory yes it could be all 60 months but I expect nobody will need to use more than 15. It is necessary to allow for each of the 60 dates to be used if required as the dates people will have data for will vary.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. design data entry forms to make a double entry
    By Paacweci1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-01-2019, 08:45 AM
  2. [SOLVED] Make udf flexible to specific sheet
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 09-29-2016, 11:04 AM
  3. [SOLVED] Make UDF function flexible to any sheet
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-18-2015, 06:14 AM
  4. [SOLVED] How to make a flexible line chart in Excel
    By BNCOXUK in forum Excel General
    Replies: 3
    Last Post: 10-30-2012, 12:26 PM
  5. conditional sum: can I make the array flexible
    By jash147 in forum Excel General
    Replies: 4
    Last Post: 11-02-2007, 02:25 AM
  6. Can I make formulas more flexible?
    By George in forum Excel General
    Replies: 3
    Last Post: 11-08-2005, 01:40 PM
  7. How to make a flexible cell reference?
    By Mar Vernooy in forum Excel General
    Replies: 2
    Last Post: 07-01-2005, 09:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1