converging tabs

[Andrianna]

I have one column of numbers on a spreadsheet with about 10 different
tabs. I would like all of the columns to sit next to eachother on one
tab.

Thanks for your help!
Andrianna

[Max]

"Andrianna" wrote:
> I have one column of numbers on a spreadsheet with about 10 different
> tabs. I would like all of the columns to sit next to each other on one
> tab.

Assuming data is all within say A1:A10 in each of the 10 source sheets

Enter the 10 source sheetnames into B1:K1
(Sheetname entry order is immaterial, your design/placement choice)

Then put in B2:
=OFFSET(INDIRECT("'"&B$1&"'!A1"),INT((ROW(A1)-1)/10)+MOD(ROW(A1)-1,10),)
Copy B2 to K2, fill down to K11 to populate the table

The above will return the required results, ie extract what's within A1:A10
from each source sheet and place it under the sheetname col header

Ensure that the sheetnames entered into B1:K1 match exactly (except for
case) with what's on the tabs, otherwise we'd get #REF!. Watch out for
inconsistencies: typos, extra white spaces, etc.

Empty source cells if any, will be returned as zeros.
For a neater look, we can suppress the display of extraneous zeros in the
sheet via clicking: Tools > Options > View tab > Uncheck "Zero values" > OK

Adapt to suit: change the "10" within the INT(..) and MOD(..) to a number
corresponding to the number of cells within the source range, then copy the
formula down accordingly by that number of rows ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Max]

Oops, correction to the formula ..

> .. in B2:
> =OFFSET(INDIRECT("'"&B$1&"'!A1"),INT((ROW(A1)-1)/10)+MOD(ROW(A1)-1,10),)

Put in B2:
=OFFSET(INDIRECT("'"&B$1&"'!A1"),MOD(ROW(A1)-1,10),)

(the INT part wasn't necessary here)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---