copy

[Khalil Handal]

Hi,
I have 12 sheets for 12 months. I have another sheet for summary from the 12
sheets. From each of the 12 sheets i need to copy 15 cells.
Is there a way to write the formula once and then pull down instead of
writing each one manulay using the name of the sheet from which I copy.

Khalil

 

[Max]

One way is to use INDIRECT()

Assume the monthly sheets are identically structured
and named: Jan, Feb, Mar ...

Assume the 15 cells to be extracted from each monthly sheet
are, for e.g.: cell B2, C3, D4, E7 ... etc

In "Summary"
------------------
List across in B1:M1, the 12 monthly sheets: Jan, Feb, Mar ...
List down in A2:A16, the 15 target cell refs: B2, C3, D4, E7 ... etc

Put in B2: =INDIRECT("'"&B$1&"'!"&$A2)

Copy B2 across to M2, fill down to M16
(or copy down and fill across to populate the grid)

The above will extract what's in the 15 cell references
from each of the 12 monthly sheets

And for a cleaner look, we could also suppress extraneous zeros
from showing in the sheet via:
Click Tools > Options > View tab > Uncheck "Zero Values" > OK

 

[Khalil Handal]

Hi,
I tried what you sent to me but i have an error: #REF!
I don't know what went wrong or if i was not clear!

To be more specific:
12 sheets have the anmes of twelve months.
sheet 13 has the name of "summary"
in cell c5 of sheet13 for example I need to copy from sheet1(january) the
range f10:k10
in cell c6 of sheet13 I need to copy from sheet2
(february) the range f10:k10
and so on

thank you.

 

[Max]

I tried what you sent to me but i have an error: #REF!

Your *actual* sheetnames probably did not match
what was entered in B1:M1, that's why <g>

Ok, since you've given more specifics
on your layout and reqts, we could try this instead:

Assume you have the 12 monthly sheetnames entered in A5:A16,
viz.: January, February, March ... December

Put in C5:
=OFFSET(INDIRECT("'"&$A5&"'!F10"),,COLUMNS($A$1:A1)-1)
Copy C5 across to H5 (i.e. across the same range size as F10:K10),
then fill down to H16

C5:H5 will return what's in F10:K10 in "January"
C6:H6 will return what's in F10:K10 in "February"
and so on ..

 

[Khalil Handal]

thanks a lot.
Khalil

 

[Max]

You're welcome !

 

[Khalil Handal]

Hi Max,
it is me again
What if the sheet name is "January 2005" and need to have only the first
word "january" without the "2005"
what changes should i make to your formula?

=OFFSET(INDIRECT("'"&$A6&"'!F10");;COLUMNS($A$1:A2)-1)

Khalil

 

[Bob Phillips]

Maybe

=OFFSET(INDIRECT("'"&$A6&" "&YEAR(TODAY())&"'!F10");;COLUMNS($A$1:A2)-1)


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Max]

With A5:A16 containing: January, February, March ... December
(no change)

Put this slightly revised formula in C5 (the top left starting cell):
=OFFSET(INDIRECT("'"&$A5&" 2005"&"'!F10"),,COLUMNS($A$1:A1)-1)

Copy C5 across to H5, fill down to H16 as before

The change made was to concat " 2005" (note the preceding space within the
quotes) with what's in A5, A6 ... A16 to match the *actual* sheetnames:
January 2005, February 2005, etc