Display report data rolled Up?

Phil Smith

Hi there

 

I’m currently running an exercise where we are asking a group of 30 users 10 sets of questions each. Each set of questions has its own web form so there should be 10 responses from each user (300 separate rows in total). I have this working fine.

 

The users have requested a ‘dashboard’ which would allow them to see how many questions they have left to answer. I can show each answer as ‘Red’ or ‘Green’ depending on if they have answered the question. I have this working fine.

 

I can create a report for the but the report shows each submission on a separate ROW. Ideally I want just one row per user with the 10 questions along the row (the submissions ‘ROLLED UP’ and grouped by user). Is this possible?

 

I have also been trying to see if there is a way to have a formula in one sheet reference data in another sheet as this would allow me to achieve what I am trying to do via the reports, but I can’t see anywhere that shows how to set formula up across sheets. Is this possible?

 

Any help or guidance greatly appreciated.

J. Craig Williams

Answer to first question, yes it is possible.

I'd have to see the way you have laid out the sheet to be sure of my answer.

But you are probably looking for either COUNT, COUNTIF, SUM, SUMIF or some combination of them to get things into one row for each user.

 

Answer to second question. No, currently formulas looking at other sheets is not supported. You can sometimes work around this by linking data from one sheet to another and then keying your formula off of that.

 

Hope this helps.

 

Craig

Phil Smith

Hi Craig

 

I have attached two screen shots.

1. Is the data as it comes in when the forms are submitted (I have blanked out the names)

2. Shows groups that are all from one user. There are two users submissions show.

 

The top bar shows all of the sections that will be answerd. If a user has not submitted an answer I need the box to be 'red ball', where ther do submit an answer, I would like it to show 'green ball'. There are 29 questions split over 8 sections.

 

Hope this makes sense.

J. Craig Williams

Phil,

 

OK, let me see if I have this right.

 

In the first picture (the color coded one), rows 42-49 are the same user responding to 8 different WebForms. 

The first WebForm is for section 1 and has 3 questions (1.1, 1.2, and 1.3)

 

Based on row 58 & 59, the user does not need to do the sections in order.

 

There are one or more columns between each question column and they aren't relevant to the problem.

 

If I have that right, let's move on.

 

I don't see any unanswered questions for the users that answered rows 42-49 and 50-57.

 

Where is the box you refer to for red=no answer and green=answer reside?

Is there one per question column or one for the entire set of questions?

 

It looks like you have entered all of user 1 and then all of user 2.

Is that always the case? Are you moving rows to consolidate them after they have been entered?

 

Will they be taking the 'test' more than once? If they skipped a question today, do they come back tomorrow with the knowledge that they need to answer 1.3 (hopefully from the report you generate)?

 

I think I see it, but something is eluding me.

It might be those hidden columns.

 

Craig

 

 

 

Phil Smith

Yes you are right on your first comments. The 'hidden' rows between the scores are for them to add 'comments' as to why they scored something a particular score (their rationale). These can be ignored.

 

The red and green box is also hidden. I was using these with a ISNUMBER statement to identify if the user had completed a score. I think that the ISNUMBER logic is still relevant as part of the rolled up data but probably not required within the actual data.

 

All of the data being submitted will be mixed up in the list. User 1 will submit some answers, then user 2, user 5, user 1 etc....They wont be in any particular order. To make matters worse, some users will not submit anything for a period of time so I still need to be able to show them as a rolled up row (user) with their 'non-submissions' showing as a red ball.

 

Users can take the test several times but I have a flag set which triggers an alert so that I can go into the data and remove the previous score(s). 

 

To sum up.

1. I just need to show in a table or grid that they have submitted answers or now (I'm not worried about what the scores are). Each question box just needs to have a 'red' or 'green' ball.

2. If they submit muliple entries, that's fine. it will just show as 'green' unless we can add some additional logic to change the ball to 'blue' if there are muliple entries for the same user/same question.

3. I suspect that I need to insert a row for 'each of the users' above the data rows (submissions from the forms) and then add a formula to check the column that is the username and each of the 29 questions. If there isa number then show the 'static' row for the user as a green ball (per question), otherwise show it as red ball.

 

I hadn't thought about adding a static table of users above the dynamic data until you mentioned the formula options in your original reply.

 

Just need to work out the formula as per item 3 above.

 

Hope this makes sense.

J. Craig Williams

Phil,

 

It does make sense.

The user rows at the top are the way to go.

 

If you create a copy of the sheet, delete all the data, and share it to me:

 

jcwill23@gmail.com

 

I'll try to take a quick look.

It will save me time on the building of the column names and such.

I know how I would implement it, I just need to test it.

 

Craig

J. Craig Williams

So, Phil shared the sheet with me.

Here's what I came up with:

 

 

I put it in the Primary Column because I could not add columns.

But better is a Contact List column, probably.

 

In the "S" columns, I added a formula. It goes here instead of the question number column to avoid circular references.

 

=IF(COUNTIFS([1.1]:[1.1], ">" + 0, $[Created By]:$[Created By], $[Primary Column]41) > 0, "1.1 complete", "1.1 incomplete")

 

if the count of matches for name and the number of answer is greater than 0, then

the question is complete

otherwise

not complete.

 

Phil will of course change the text of the results.

Maybe to a number to count them or some such.

 

The $ makes the reference absolute (to Created By and Primary Column columns for ease of copying to the next one.

If you go with the current name, you can copy and paste to everything but need to change the text from "1.1 ..." to the correct column/question.

 

Phil tested and it works for him.

So we are both happy.

 

Craig