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:
Points to note:
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
ASKER
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
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
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
ASKER
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:
Of course, there may be a completely more effective way of achieving this, I am open to ideas.
Cheers
Andy
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
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
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
ASKER
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
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
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
ASKER
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
On the odd occasion, the Strongly Agree etc changes to 1st, 2nd, 3rd, Other, but there are still just the 4 options
Andy
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
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
ASKER
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.
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.
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
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
ASKER
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
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
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
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
ASKER
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
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
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
gowflow
ASKER
Yes, I think it will be fine to assume that
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
ASKER
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
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
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
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
ASKER
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
Andy
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
If the idea is to only print in hard copy the the first option of producing all the graph is better.
Your choice.
gowflow
ASKER
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
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
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
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
ASKER
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.
tks for your nice comments which much appreciated especially coming from an expert !!
Rgds/gowflow
Rgds/gowflow
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