3-D range into column

[Guest]

I'm analyzing a set of political science surveys. Each survey has its own
worksheet in the same workbook. I'm creating a summary on which I want to
show two sets of results from each survey - so I want two columns, one of
which shows A2 from each worksheet, and parallel, A4 from each worksheet.
You can't create lists across difference worksheets, though. Can anyone help?

 

[Guest]

Rebecca,

I'm not sure I understand exactly what you are looking for.

Are you saying, you have sheets: Sheet1, Sheet2, Sheet3, SummarySheet.

On Summary sheet, you want:

A B
1 Sheet1!A2 Sheet1!A4
2 Sheet2!A2 Sheet2!A4
3 Sheet3!A2 Sheet3!A4

?

 

[Guest]

Yes - that's exactly it. There must be a straightforward way to do this, but
I've been working at this for a couple hours and haven't figured it out.
Thanks,
Rebecca

 

[Guest]

Great.

So, does my answer suit your needs? Or are you looking for an automatic way
do this? How many sheets are in the workbook?

What is the naming convention (if any) for the sheets?

 

[Guest]

Well I'd prefer an automatic way, since there are about 60 sheets, with
possibly more to come. Each sheet is named with the number of the survey (id
Test24) but they are not consecutive, as a number of them aren't being
included.

I want to try to name the range and then input the name as the source for a
pivot table, but this really isn't working and I don't know why.

 

[Guest]

Hey, wait a sec - if that's exactly what I want, is there any way I can start
it by manually entering what you show, then dragging and filling? The
obvious way isn't working, but is there any way to get it to go sequentially
through the third dimension, and not through a column or row?

 

[Guest]

Rebecca,

Well, there is probably a couple ways to automate this.

How about this?

In column E, type the test numbers you would like to include, starting on
the row you want to start collecting data in column A.

For example:

A B CD E
1 Header Header Header
2 Formula Formula 1
.................. 2
............... 6

etc.

Then, in cell A2, use this formula

=indirect("id Test"&e2&"!A2")

Copy this formula down

In cell B2, use this formula

=indirect("id Test"&e2&"!A4")

Copy this formula down


Is that any easier?

 

[Guest]

Rebecca,

Also, if you were to use every survey you could cut out the part of typing
which surveys to include in column E and use something like this:

=indirect("id Test"&row()&"!A2")

This would start using the survey with the number of the row, in our
example, number 2. If you wanted to start at one, you could modify to read:

=indirect("id Test"&row()-1&"!A2")

You could even use this instead of the previous solution. Simply copy the
formulas down. Then select all of them, copy and paste special:values. Then
delete the entries you don't want to include.

Hope that helps.