How do I collate data from 36 sheets into a summary sheet?

[sindora]

Hi, could someone help me with a problem?

I need to pick 2 cells from 1 worksheet (ditto for 36 worksheets), and collate the info onto a Summary sheet (see attached sample).

Per the sample, I'm trying to pick D26 and D31 from each of the 36 sheets. This will be placed in a 2 column table in the Summary sheet.

I'd need to do this every month, and it may be a different item the next month...(eg. could be D24 next month).

Is there a simpler way than copy-and-paste? A colleague told me to use macros, but I'm code-illiterate. I've tried to search the web for something similar, but due to my limited understanding, I'm unable to modify very much what I've found. For eg. I found this --> http://www.rondebruin.nl/copy2.htm.

Can someone help me with a simpler macro? I'm capable enough to change the cell, if it's not too complex. Thank you very much in advance.

[StephenR]

You could try this formula in B2 and copy down, then do similar for C2:

=INDIRECT("'"&A2&"'!D26")

[sindora]

BINGO StephenR! Thank YOU!

May I ask what do those quotes, double quotes, & and ! mean? It'll help me modify this formula for future use...

[StephenR]

With Indirect what we are doing is building up a string which is equivalent to a cell reference, in effect what you would do if you were entering the formula directly. If you were doing the latter you would write

='Sheet1'!D26

so we have to incorporate the single quotes and exclamation mark because that is Excel's syntax.

=INDIRECT("'"&A2&"'!D26")

[sindora]

Thank YOU, again. <Deep bow>

[StephenR]

No problem. Don't get stuck down there.