Copying data from Muliple Sheets (Same Cell) to a new Sheet (Column)

I have a spreadsheet with 563 sheets. On Sheet 1 (blank), Column B2, I'd like to pull data from Sheet 2, cell C10. I'd actually like to pull all of the data from each Sheet's C10 cell into the respective row on Sheet 1, i.e.:

Sheet2,C10
Sheet3,C10
Sheet4,C10
...
Sheet 563,C10

I've learned from this Board (other postings) that I can use the following formula, however, it's not allowing me to easily "drop & copy the cell's formula" (using the + in the lower right corner & dragging down the cell's formula to the cell below).

=INDIRECT("Sheet" & ROWS($2:1) & "!C10")

Please keep in mind that while I understand what the cells pull in, I don't fully understand why, so I'm pasting the cell formula so that you have an idea of where I can start. I tried the response that said to insert an apostrophe in place of the Sheet name, but that didn't work for me.

THANKS in advance for any help that you can provide!!!

Scooter

No, I prefer to have the Value instead of the Sheet name. I was just trying to illustrate how I'd like for the values to go down, pulling from Sheet names (across a Workbook).

Thanks!

That's the right way to pull the data, I'm just not able to copy the formula down in order to pull each of the rows' Values for 563 rows. If I copy the formula as-is above it just copies the same cell value all the way down the column. Obviously the cell Value will change from sheet-to-sheet.

Does this help?

Here's the text from the original posting that I tried to copy. Omar had the same problem but didn't have any problems getting his formula to work.

****************
Can anybody teach me how to copy data from multiple worksheets, to a new worksheet? I need the same cell from each worksheet. For example, i have 300 worksheets, i need to copy each data value in each b4 cell from each sheet onto the 301st worksheet, and have each data value one after the other in the format of a column.

is this possible??

***************

Thanks for your patience!

No, I'm not copying individually. In the original post I mentioned the + sign/copy method.

I'll just do it manually. Thanks anyway.

Thanks, Speshul. I don't know how else to explain it. Sorry. The alternate formula didn't work. When I copy up or down it just copies the same data from each Sheet1,B1, Sheet1,B2, etc.

I need Sheet1,B1 to reflect Sheet2,C10, Sheet1,B2 to reflect Sheet3,C10 and so on...

Success! Thanks!!