trickard1000
New Member
- Joined
- Apr 4, 2018
- Messages
- 1
Hi. I am a noob! So, apologies if I have missed a post that explain this, as I am unsure of the language to use to describe it.
I have 200 questionnaires in the form of workbooks with several sheets. The data is almost all quantitative, percentages or numbers with two decimal places, but there is the odd X or 'no' and many cells are empty. The key data of interest resides on two of the sheets of each workbook, and every respective sheet has the same structure. Unfortunately, this structure is a bit haphazard, if consistent, and not presented as tables. It was designed to use in the field, with embedded formulas to create simple sustainability indicators for small farms.
I want to create one table with every single type of answer as a header, perhaps 100 headers, along the top row, and the results for each questionnaire can sit in its own row. Then I can filter, right?!
I can create the structure for this manually, placing each header of interest along the top. But, I want to automate the process of extracting the relevant information from the equivalent cell in every new workbook and putting it in its own row. I have been experimenting with placing all sheets of interest into one workbook, I am not sure if that might help. I imagine I can tell Excel which cell to look in for the information in one workbook or sheet, and then get it to repeat the same process for different sheets or books.
Many thanks for any tips or help you can give.
Regards,
Thomas.
I have 200 questionnaires in the form of workbooks with several sheets. The data is almost all quantitative, percentages or numbers with two decimal places, but there is the odd X or 'no' and many cells are empty. The key data of interest resides on two of the sheets of each workbook, and every respective sheet has the same structure. Unfortunately, this structure is a bit haphazard, if consistent, and not presented as tables. It was designed to use in the field, with embedded formulas to create simple sustainability indicators for small farms.
I want to create one table with every single type of answer as a header, perhaps 100 headers, along the top row, and the results for each questionnaire can sit in its own row. Then I can filter, right?!
I can create the structure for this manually, placing each header of interest along the top. But, I want to automate the process of extracting the relevant information from the equivalent cell in every new workbook and putting it in its own row. I have been experimenting with placing all sheets of interest into one workbook, I am not sure if that might help. I imagine I can tell Excel which cell to look in for the information in one workbook or sheet, and then get it to repeat the same process for different sheets or books.
Many thanks for any tips or help you can give.
Regards,
Thomas.
