Data transferring and report/chart printing.

Lacostee12

New member
Joined
Jun 20, 2011
Messages
11
Reaction score
0
Points
0
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? :confused2:

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!!
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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...
 

Lacostee12

New member
Joined
Jun 20, 2011
Messages
11
Reaction score
0
Points
0
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!
 

Simon Lloyd

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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.
 

Lacostee12

New member
Joined
Jun 20, 2011
Messages
11
Reaction score
0
Points
0
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.
 

Attachments

  • Hymiojutska_aloitusideointi.xls
    89.5 KB · Views: 24

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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.
 

Attachments

  • Hymiojutska_aloitusideointi.xls
    109.5 KB · Views: 31

Lacostee12

New member
Joined
Jun 20, 2011
Messages
11
Reaction score
0
Points
0
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. ?
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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,
 

Lacostee12

New member
Joined
Jun 20, 2011
Messages
11
Reaction score
0
Points
0
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!
 

Attachments

  • Hymiojutska_aloitusideointi.xls
    54 KB · Views: 28

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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.
 

Attachments

  • Hymiojutska_aloitusideointi.xls
    78 KB · Views: 40

Lacostee12

New member
Joined
Jun 20, 2011
Messages
11
Reaction score
0
Points
0
Yes thank you!! Yeah only what i will need anymore is that get those emotions names in that chart but if it isn't possible its ok :) I can't thank you enought about helping me in this! I would never get this done without your help!
 

Lacostee12

New member
Joined
Jun 20, 2011
Messages
11
Reaction score
0
Points
0
OH and one thing still i'm sorry.. Is there possible to do like automatic save. I mean when someone votes by pressing answer button, so is there any chance to program it doing automatic save for excel workbook same time?
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
oh yeah, that part is easy...

Right click on the Query sheet tab and select "View Code". That should take you right into the correct code module. Right before the line that says "End Sub", put in the following:

Code:
Thisworkbook.Save
 

Lacostee12

New member
Joined
Jun 20, 2011
Messages
11
Reaction score
0
Points
0
Thanks! Now i can start make layout looking good. If you get idea how to get see emotions in chart so let me know, but if not its ok because its not so important.
 

Lacostee12

New member
Joined
Jun 20, 2011
Messages
11
Reaction score
0
Points
0
its me again. Is it possible that when i click answer button it same time refresh the pivotchart also? Thank you again!
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Sure. In that macro, put the following below the "End With" line (just above the 'Let the user know of success" line)

Code:
'Refresh the pivot table
Worksheets("Report").PivotTables("PivotTable2").PivotCache.Refresh

If you are using your own workbook, you'll need to update "PivotTable2" to the name of your pivot table.
 
Top