please help with formula for same cells in different sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please can some one help me,
I am trying to get a quick way of coping a formula without having to manualy
change the sheet name.

example:

sheet 10 formula consists of adding b3 + b4 + b6 + b 9 + b23 from sheet1

now i want to copy this formula for sheet 2, but without having to manually
change the "name" from sheet1 to sheet2

my current formula looks like


=(+jan 04'!$f17+'jan 04'!$f19+jan 04'!$f25)

i now want the cell next to the cell with the above formula to read
=(+feb 04'!$f17+'feb 04'!$f19+feb 04'!$f25)

thank you in advance
 
One way ..

Assuming you have listed across
in B1, C1, etc the "months":Jan, Feb ... etc

then you could put in say B2:
=INDIRECT("'"&B1&" 04'!F17")+INDIRECT("'"&B1&" 04'!F19")+INDIRECT("'"&B1&"
04'!F25")

This returns in B2 the functional equivalent of:
=(+jan 04'!$f17+'jan 04'!$f19+jan 04'!$f25)
Click to expand...

Just copy B2 across to C2
to return the equivalent in C2 of:
=(+feb 04'!$f17+'feb 04'!$f19+feb 04'!$f25)
Click to expand...

and so on
 
Hi Max, i have similar hassle. each of my worksheets has a persons name.
Such as David, james, Fred.
On one front sheet, I am trying to combine all the info. Example: Overtime
(will read off same cell but on 20 differnt sheets). Like Freddie wrote, i
don't want to sit and change the names for each cell.
On front sheet i have B1 James, B2 Fred, B3 David etc
In C1, C2, C3 etc i want to read the overtime rate (H10) from each of their
sheets.
Going wrong somewhere with the formula you suggested for freddie. can you
help. Thanks Ali
 
On front sheet i have B1 James, B2 Fred, B3 David etc
In C1, C2, C3 etc i want to read the overtime rate (H10)
from each of their sheets.
Click to expand...

Put in C1:
=INDIRECT("'"&B1&"'!H10")
Copy down

---
 
Thanks Max, works like a charm. Must have been missing an apostrophe
somewhere. Much appreciated
 
Back
Top