reference & view same cell location from multiple sheets

[ffsienk]

Forgive me if this is a newbie question.

I have multi, multi sheets, all of the same format. Each sheet calculates several similar values, but for a different set of data.

What I ultimately want is to view and then manipulate the data in the same cell location found on each of these sheets on one sheet.

For example, I want to list the data value from cells =sheet1!a1, =sheet2!a1, =sheet3!a1, through =sheetN!a1 on one new 'comparison sheet', without cutting and pasting each cell.

Autofill doesn't fill the need.
I can get it to reference the same cell across multiple sheets.

Wondering if I setup the format for the data in each sheet poorly, making this multiple sheet analysis more difficult than it needs to be.

Aside: I've never written a macro, so it
might be time to venture and invest the time to do so.

Thank you for any assistance offered.

 

[Bob Greenblatt]

Forgive me if this is a newbie question.

I have multi, multi sheets, all of the same format. Each sheet calculates
several similar values, but for a different set of data.

What I ultimately want is to view and then manipulate the data in the same
cell location found on each of these sheets on one sheet.

For example, I want to list the data value from cells =sheet1!a1, =sheet2!a1,
=sheet3!a1, through =sheetN!a1 on one new 'comparison sheet', without cutting
and pasting each cell.

Autofill doesn't fill the need.
I can get it to reference the same cell across multiple sheets.

Wondering if I setup the format for the data in each sheet poorly, making this
multiple sheet analysis more difficult than it needs to be.

Aside: I've never written a macro, so it
might be time to venture and invest the time to do so.

Thank you for any assistance offered.

I¹m not sure what you want to do. I don¹t understand: ³list the data value
from cells =sheet1!a1, =sheet2!a1, =sheet3!a1, through =sheetN!a1 on one new
'comparison sheet²
You can¹t have a single cell contain multiple values. If you want the sum of
that cell from the other sheets, for example, in the new sheet type an equal
sign in the cell where you want the sum, then click on the sheet tab for the
first sheet, and then click on the cell, click on the tab for the second
sheet and then click on the cell. When you are done, press enter.

 

[ffsienk]

Thanks for responding Bob.

ok, I'll try to be clearer.

Example: In Sheet1, I calculate the standard deviation of a set of 245 data points. That value is in cell A1 on Sheet1.
Another set of data points, 160, I calculate
the standard deviation for this set, in Sheet2,
and so forth.
Now, I want to compare the standard deviation of set of data points (found on separate sheets).
So I open a new sheet (called Comparison) and
list these values by '=Sheet1!A1' in one cell B1,
then 'Sheet2!A1' in the next cell B2.
Easy.
But what if I have 50 sheets, with 50 standard deviations calculated from 50 different sets of data?
Is the only way to call these 50 different values calculated from a separate independent data set (and found at the same cell location on similar formatted Sheet1 through Sheet50)
is to cut and paste OR
type '=Sheet1!A1' into one cell on the 'Comparison' sheet, then '=Sheet2!A1' into the next, etc. until repeated 50 times???

I want to display a list of the same calculated quantity from different data sets on one worksheet.

Is that more clear?

Thanks...

 

[Bob Greenblatt]

Thanks for responding Bob.

ok, I'll try to be clearer.

Example: In Sheet1, I calculate the standard deviation of a set of 245 data
points. That value is in cell A1 on Sheet1.
Another set of data points, 160, I calculate
the standard deviation for this set, in Sheet2,
and so forth.
Now, I want to compare the standard deviation of set of data points (found on
separate sheets).
So I open a new sheet (called Comparison) and
list these values by '=Sheet1!A1' in one cell B1,
then 'Sheet2!A1' in the next cell B2.
Easy.
But what if I have 50 sheets, with 50 standard deviations calculated from 50
different sets of data?
Is the only way to call these 50 different values calculated from a separate
independent data set (and found at the same cell location on similar formatted
Sheet1 through Sheet50)
is to cut and paste OR
type '=Sheet1!A1' into one cell on the 'Comparison' sheet, then '=Sheet2!A1'
into the next, etc. until repeated 50 times???

I want to display a list of the same calculated quantity from different data
sets on one worksheet.

Is that more clear?

Thanks...

OK, much clearer now. So if you want column A on a new sheet to have the
value from sheet1 A1 in row 1, and the value from sheet2 A1 in A2, etc. then
enter this formula into A1 on the summary sheet:
=INDIRECT("sheet"&ROW()&"!$a$1")
Once it is entered, drag the fill handle in the right hand corner down for
as many cells/sheets as you have.