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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Danerida

Board Regular
Joined
Aug 25, 2014
Messages
152
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
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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
 

douglasX

New Member
Joined
Sep 14, 2014
Messages
2
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,472
Messages
5,601,854
Members
414,479
Latest member
Beau the dog

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
Top