Andrianna said:
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 ..
