Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

Thread: Data transferring and report/chart printing.

  1. #1

    Question Data transferring and report/chart printing.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    I have a big problem. I should a do excel workbook where person can aswers his feeling in every week. There is 12 different persons who can aswer this and you have to choose who is aswering from a dropdown option. Then there should be 5 different options (maybe with optinobuttons) about emotions (Great,good,ok etc.) and one "Send" -button which should register this press to table in an another sheet.

    In another sheet, there should be collective tables for every person for week about aswering and emonitional choise. I can make these tables and dropdown option.

    Can anyone help me how i program to register aswers from another sheet to tables so it's depends about choise in dropdown option, week and emotional choise in option buttons? I'm not sure if excel can identify week automatically or should that been written also in first sheet?

    I also need help to get reports/charts weekly, monthly and yearly from aswers. Reports should be able to get by person or all together.

    Is that all possible? I'm amateur so i really need help. I can do something with Visual Basic but i need insrtuctions for these issues.


    Thank you!!

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Welcome to the forum!

    That shouldn't be an issue, but a couple of questions for you first...

    1) What version of Excel are you using?
    2) How far have you got so far?

    I would start by making a worksheet table that lists the names, and the emtional states you want to track. After that, we can work on capturing the data. (Upload it to the forum so we can tweak/review it to make it optimal first.)

    My suggestion would be to use a userform to capture it, write the data to a table, and then use PivotTables and PivotCharts to pull out the data in the form you need it...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Hello,

    Thank you. I'm using Excel 2003. I could give you a names and states. I'll send them in private message. I haven't started excel because this problem did appear at then first part. I'm really glad if you could help me to do this programming part! I can modify it to looks what i need! Many thanks!

  4. #4
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    0
    Unfortunately as a free service we cannot build the workbook for you, you need to go some way in to structuring as Ken pointed out and then post your efforts here, we can then help you further refine that, remember that your data type and structure should remain the same as your real workbook when uploading.

    Without seeing your structure and layout it is very difficult to help.
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  5. #5
    Okey, i understand. here is my "workbook". It should work something like that. Can you please check is that possible to do like that and is there any tips for layout/data gathering/tables how it is easy to program.
    Attached Files Attached Files

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Hi there,

    I've made some changes for you, but I don't have time for any more at the moment. Here's what I've done:
    • Added a "Tables" worksheet and put your names and emotional states there
    • Used named ranges on those
    • Changed your data validation to pull from the table (easier to update)
    • Change your ActiveX option buttons to Forms option buttons
    • Linked those to cell B8
    • Implemented your Week and Year based on today's date (this will always update to current day)
    • Summarized the results in columns A:B (you will hide those when you deploy this)
    • Programmed the button to write the results to the HistoricalData worksheet
    On that worksheet there is a table that is always updated so that the data can be tackled using the range named rngHistoricalData.

    Now, the next step here is to build some PivotTables to generate the data you need for your report. Build the pivot tables on new worksheets, using that named range as the source.

    Let us know where you get stuck.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Thank you thats working great!! awesome. I have done one pivot table what gathers information. Only problem is that my excel wont identify week automatically. Theres formula =WEEKNUM(TODAY()) but it show to me only "#NAME?". You guys got idea what is wrong with that?

    One more thing also. Is there possible get some "popup" when you press aswer button like "Your emotion has been registered" etc. ?

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Ah, sorry. The WEEKNUM formula is part of the Anaylsis Toolpack. To activate it, go to Tools-->Addins and check the box next to Analysis Toolpack. The only issue is that each of your users will need to do this. For reference, we can also use code to ensure that the analysis toolpack is activated whenever a user opens the workbook.

    Regarding the popup, sure thing. Press Alt+F11 to get into the VBA Editor (VBE). Find your workbook in the Project Explorer. You'll need to drill down into Microsoft Excel Objects-->Sheet 1(Query). Once you double click that, the code pane should open.

    Right before the line that reads Application.CutCopyMode=False, enter the following code:
    Code:
    Msgbox "Your data has been transferred.  Thank you!"
    (You can obviously change the message to suit.

    Cheers,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #9
    Now it seems to work fine! Thank you.

    There is two more things where i maybe need bit help. Is it possible to do some kind of condotion that same person cant vote twice in same week. Like some conditon between Query sheet and historicaldata sheet depending on week and name? I see in my mind that if same person try vote second time in same week there will be appear popup message that "You have voted already at this week".

    Second thing is pivot report. I would like to get line chart where in x-axis is week, y-axis emotion and the line color depends person? Is that possible. I tried myself but i could do that.

    Thank you!
    Attached Files Attached Files

  10. #10
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    Not sure how the chart will come out here, and I'm going to need to figure out how to get the emotion names into the pivot chart, but is this what you're trying to accomplish?

    FYI, try changing names/weeks too. I think you'll find it warns you now.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Page 1 of 2 1 2 LastLast

Posting Permissions

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