Excel formulae/sheet linking

Similar to the question i asked before, Previous question, i am hoping someone can guide me through this task.

I attach a copy of an excel workbook with a sheet titled 'Import' - this sheet is raw data coming from an online survey tool.  I want to do the following:
Add a totals row
Then extract the totals row data as shown in Sheet 2
Produce graphs as shown in Sheet 3

Points to note:
Looking at Sheet 2, Col A contains the main question with Col B showing sub questions for the Col A topic
Also looking at Sheet 2, Col A and Col B, the questions will not be same each time so I want to extract the relevant question data in these 2 columns with the appropriate totals displayed accordingly
Survey-Monkey-Trial.xls
LVL 7
andymacfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
Hi andymacf,

I see your question has been neglected for almost 2 days now and presume is for the multi questions asked in 1 single question.

I am willing to help you. I will ask you to pls reduce the number of sub question to 2 maximum so we can get efficient and you can get what you want and fast.

I will leave you to decide what are the 2 items you want to get covered in this question and if satisfied you can then close this question and ask a new one related to this one with the next items you need covered.

This way you can get more attention to your question and get result.
gowflow
0
andymacfAuthor Commented:
Hi gowflow

Ok, that makes sense.

My real technical issue is the automatic cross-matching of the items onto Sheet 2 and if you could deal with this, that would be great.

I will happily create a new question if we feel it is necessary.

Cheers Andy
0
gowflowCommented:

My real technical issue is the automatic cross-matching of the items onto Sheet 2

ok lets be precise on this request.

Pls provide a sample on how it looks now and how you would like to see just pick 1 or 2 specific rows and explain. when explain pls be specific on sheet name and row number and column so what is obvious to you may not b for others who ignore the subject. moreover if you feel the info is repetitive like same value can be found in more than 1 column or more than 1 row then you need to specify what other criteria need to be used to choose the specific value.
gowflow
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

andymacfAuthor Commented:
Ok here goes.

The Import worksheet will always be the laid out in the same format.  Assuming the totals row has been added as per the sample.  If we expand columns A - D in Import, you will see that there is one question with 4 criteria so:

Import!A1 maps to Sheet 2!A3.  This value may cover more than one question, in the sample the question at Import!A1 is used in Sheet2!A3 - A5
Import!A2 maps to Sheet2!B3 - Also need to extract the data before the hyphen
import!A169 maps to Sheet2!C3

Of course, there may be a completely more effective way of achieving this, I am open to ideas.

Cheers
Andy
0
gowflowCommented:
Clear
more questions

1) Can we assume that the sheet that has the data will always be called Import ? or it can be an other name that varies ?

2) the output sheet is always sheet2 ? or shall we call it Output with a datetime so that if you run it now it would create Output 20131002 1428
and if you run it tomorrow say at 1005 AM it will addd a sheet in the workbook called
Output 20131003 1005 this way you have a workbook that keep trace of older versions and you can manually delete or keep which ever you like.

Let me know.
gowflow
0
andymacfAuthor Commented:
1. Yes, the data sheet will always be called Import

2. I would prefer to keep it as 'Output' because I will be using each instance of the data merge to generate the graphs i mentioned earlier.

Other point to note - the questions may differ so the titles are not always fixed

Cheers Andy
0
gowflowCommented:

Other point to note - the questions may differ so the titles are not always fixed

If the content of the question changes that is no problem.
Will the options changes ? now you have 4 can it be some questions are at 4 some will have a different scale like 6 etc ... or always 4 ?

gowflow
0
andymacfAuthor Commented:
Hi gowflow

On the odd occasion, the Strongly Agree etc changes to 1st, 2nd, 3rd, Other, but there are still just the 4 options

Andy
0
gowflowCommented:
ok

1) and it is by that that you meant the question will change ?
2) Will always the header of the section be on row 1 ?
3) Will always the questions be on row 2 ?
4) and results starting row 3 ....

by the way you inputed for this sample file a total in the function count which I would not agree with if your figures are always numerical then the function should be Sum not count !

gowflow
0
andymacfAuthor Commented:
1) Yes
2) Yes
3) Yes
4) Yes

The figures are numerical but 1 = Strongly Agree, 2 = Agree, 3 = Disagree, 4 = Strongly Disagree so it is the fact the cell is populated that I am counting not the actual figure. I hope this makes sense.
0
gowflowCommented:
ah I see so count make sense then. last one 'sorry for asking too much' but prefer to build it correct rather then re-do coding for lack of info,

will the format of the question always be ... for example for this question
was easy to navigate - Strongly Agree

the question space hyphen space the measure

like when it is 1, 2 3 etc... I gues it will be something like
was easy to navigate - 1

will it always be in this format described here ?
gowflow
0
andymacfAuthor Commented:
Yes, the format is the same, the number of 'measures' may change e.g. in Sheet2 Col B there are some with 3 'measures and other with only 2 'measures'

for the 1, 2, 3 questions, it will be

'I was able to secure a booking at a test centre of my - 1st choice'
'I was able to secure a booking at a test centre of my - 2nd choice'

Andy
0
gowflowCommented:
ok so I guess you contradicted yourself then
first you said always 4 then now it maybe 3 it may be 2 and maybe 4
so you would endup with something like

title                  question               measure1 measure2 measure3 measure4
                         question2
                         question3

title2                question              measureA   measureB
                         question2
                         question3

title3                question              measure X   measure Y measure Z
                         question2

title4                question               measure1 measure2 measure3 measure4
                         question2

etc....

I need the most complex final outcome to be able to build the model.
gowflow
0
andymacfAuthor Commented:
Apologies I misunderstood your terminology, you're breakdown makes it clear but the measures will always be 4

title                  question               measure1 measure2 measure3 measure4
                         question2
                         question3

title2                question              measure1 measure2 measure3 measure4
                         question2
                         question3

title3                question              measure1 measure2 measure3 measure4
                         question2

title4                question               measure1 measure2 measure3 measure4
                         question2

Andy
0
gowflowCommented:
ok fine then. So measures are always 4 but can change from description but they will still mean the same when they show 1st option 2ndoption etc... do you need then a new header or we can assume that the top header applies to all data populated in Col A and B ??
gowflow
0
andymacfAuthor Commented:
Yes, I think it will be fine to assume that
0
gowflowCommented:
Here it is.

Pls download the file enable macros and in sheet main you have 2 buttons the blue one is for this question. Activate it and check the results.

If you like it then we can attack the graph issue in a related question. But prior that I would need some clarifications on the graph in here before you post something new.

Will wait for your comments.
gowflow
Survey-Monkey-Trial.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
andymacfAuthor Commented:
Hi gowflow

A super piece of work which works great with this sample and I think we are almost there.  I have tried a different sample and whilst the titles and the questions are pulled across correctly the measure columns are not.  I attach it for you to have a look at.

many thanks
Andy
Survey-Monkey-Trial-V2.xls
0
gowflowCommented:
it is working perfectly.
I just ran it and look at the results. You should not put a total manually the system puts it.
gowflow
Survey-Monkey-Trial-V2.xls
0
andymacfAuthor Commented:
Hi gowflow

Apologies for that, I have tried it again and it is fine.  Not quite sure what happened there.

I will happily accept this question as complete, but you asked for clarification regarding the graphs that I need.

I have linked the graphs on Sheet3 to the data just as an example.  All graphs should be pie charts and the format I have is fine.
Survey-Monkey-Trial-V3.xls
0
gowflowCommented:
ok you gave an example on the first topic
The JCIE website

what about the remain topics you want that all in 1 sheet ? or each topic in 1 sheet ? or ....

I may have a suggestion but first will need to hear the way you see it.
gowflow
0
andymacfAuthor Commented:
I am happy for suggestions, what we want to do is produce a nicely laid out report with the title and questions.  If it is easier to put them on one sheet with page breaks between them, then fine otherwise I am happy for you to suggest something.
Andy
0
gowflowCommented:
will the objective be to print all the graphs or to have a system to choose and view on the screen the results ? 2 different approaches. If the second then we can have a combobox populated with all the questions or topic which ever you prefer and when you select 1 then it would automatically produce the graphs.

If the idea is to only print in hard copy the the first option of producing all the graph is better.

Your choice.
gowflow
0
andymacfAuthor Commented:
Good morning, although I don't know where you are in the world so it might be evening.  This being a new feature, I want to accept the previous solution and move to a new question just to obey the laws of EE. Opinion??

The objective will always be to go straight to hard copy, however, having said that if it would be possible to select which graphs to print i.e. to filter certain graphs then that would be a good design feature.

Andy
0
gowflowCommented:
well u said good morning then I reply good morning and I guess we are both on the same hemisphere !!

Go ahead and close this question and I will be glad to help you in any question you may have simply put a link here to the new one.

Regards
gowflow
0
andymacfAuthor Commented:
Obviously a very knowledgeable individual who made sure that he was clear about the requirements before proceeding and asked questions throughout to clarify my needs. Thanks gowflow, much appreciated.
0
gowflowCommented:
tks for your nice comments which much appreciated especially coming from an expert !!
Rgds/gowflow
0
andymacfAuthor Commented:
Hi gowflow

Here is the link as discussed, Graphs

Cheers Andy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.