Extracting information from multiple sheets

douglasX

New Member
Joined
Sep 14, 2014
Messages
2
Hi,

Im carrying out a social survey and to make my life easier Im inputting interview answers directly into excel. The format is as follows: questions are listed vertically in column A and answers will be typed in adjacent columns. eg

how many children do you have? ----------3
do you own a Tv?----------------------------yes
do you own a cell phone?-------------------yes
etc
etc

Each household will have a separate questionnaire, and each questionnaire will be a separate sheet on the same file.

To make analysis easier, is there a simple macro that will allow me to extract each row of information to a separate sheet so that I end up with a separate sheet for each question?

Many thanks in advance,

Doug
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
I think you would be much better served to set your data up in database table where each row is the household, and each column is your questions. You will then be able to manipulate this data to other sheets in any way you like.

If you want it laid out in a "form" style for inputting, you can lay it out the way you first described, then use a command button to transfer your data into the database table.

I think keeping each household in its own sheet is not a good way to manage this data, and will result in a very needlessly bloated file.

Dan
 
Upvote 0
Before you get too far into this, change your layout!!! Maybe something like this...
A​
B​
C​
D​
E​
F​
1​
NameNumber of childrenTVCell PhoneShoe Sizehat sise
2​
aa
1​
yn
12​
5​
3​
bb
2​
yy
11​
6​
4​
cc
1​
ny
10​
6​
5​
dd
2​
ny
12​
5​
6​
ee
0​
ny
8​
4​

This way, you can enter data into 1 Database", and use additional sheets for summaries
 
Upvote 0
Hi Dan, Sorry for the belated reply. Thanks a million for your advice. Im trying out 'household per row' now and its looking pretty good. Thanks again for your time.Cheers,Doug
I think you would be much better served to set your data up in database table where each row is the household, and each column is your questions. You will then be able to manipulate this data to other sheets in any way you like. If you want it laid out in a "form" style for inputting, you can lay it out the way you first described, then use a command button to transfer your data into the database table. I think keeping each household in its own sheet is not a good way to manage this data, and will result in a very needlessly bloated file.Dan
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top