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?

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

?

"RebeccaW" wrote:

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?

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

"Dominic" wrote:

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

?

"RebeccaW" wrote:

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?

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?

"RebeccaW" wrote:

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

"Dominic" wrote:

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

?

"RebeccaW" wrote:

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?

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.

"Dominic" wrote:

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?

"RebeccaW" wrote:

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

"Dominic" wrote:

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

?

"RebeccaW" wrote:

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?

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?




"RebeccaW" wrote:

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.

"Dominic" wrote:

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?

"RebeccaW" wrote:

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

"Dominic" wrote:

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

?

"RebeccaW" wrote:

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?

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?

"Dominic" wrote:

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

?

"RebeccaW" wrote:

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?