copying formula with worksheet name-need new formula to have anotherworksheet name

  • Thread starter Thread starter Kim
  • Start date Start date
K

Kim

I want to be able copy a formula from b5 to b6 but have the worksheet
name change, i.e.:

I have 13 sheets in my workbook; the first sheet is called Summary
which is where my formula presides. 2nd sheet,3rd sheet, etc. are
called Sal Hrs_Jan10, Sal Hrs_Feb10, etc.

My formula in b5 is =+'Sal Hrs_Jan10'!$C$15.
I want b6 to say =+'Sal Hrs_Feb10'!$C$15
I want b7 to say =+'Sal Hrs_Mar10'!$C$15

Column a, cell a5 has Jan-10 (this is a date 1/1/2010 formatted as
custom, mmm-yy).

Can anyone help me???
 
Put this in B5:

=INDIRECT("'Sal Hrs_"&TEXT(A5,"mmmyy")&"'!C15")

then copy down as required. This assumes that you also have dates in
A6 onwards - if not, then use this instead:

=INDIRECT("'Sal Hrs_"&TEXT(DATE(YEAR(A$5),MONTH(A
$5)+ROW(A1)-1,1),"mmmyy")&"'!C15")

and copy this down.

Hope this helps.

Pete
 
Back
Top