Pasting Formulas In Excel (Slightly Different)

[Mictain]

Hi All,

Thought I may have the answer ready for me by reading an earlier thread about formula pasting, but it sounds like the opposite of what I'm after, so here I am!

The formula I'm using is =SUM('Week 01'!H14+'Week 02'!H10)

Where H10 is the H6:H9 total (in hours and minutes) and H14 is the cumulative total running from sheet to sheet. So working on sheet 3 would give Week 2 H14 + Week 3 H10 - previous total plus this week's figures, etc.

I'm probably being really thick but is there a way to either tweak the formula or paste this so the formula updates with the correct sheet references each time? I have 52 sheets in each workbook, so it's getting quite tedious changing the sheet names manually and I'm getting rather bleary eyed as well!

All help appreciated. Thanks.

Neil.

 

[xlbo]

Ok - this'll give you the sheet name:
=MID(CELL("FILENAME",F10),FIND("]",CELL("FILENAME",F10))+1,255)

soooooo, you need this pasted into a cell on each sheet, say Z1.

so your new formula would be:
=INDIRECT(&quot;'&quot; & IF(VALUE(RIGHT(Z1,2))<10,LEFT(Z1,6) & (VALUE(RIGHT(Z1,1))-1),LEFT(Z1,5) & (VALUE(RIGHT(Z1,2))-1)) & &quot;'!H14&quot + H10
As long as the sheetname formula is in Z1, this will get the value of H14 in the previous week and add it to the value of H10 in the current week (sheet)


HTH
~Geoff~

 

[DuckBill]

Mictain,

xlbo's solution probably works OK (I haven't tried it), but I thought I would add my suggestion, just for the record. (It's not as elegant, but could be useful for other situations)

When I have to set up a large number of sheets which are all similar, and each one links to the previous one (a task like yours involving weekly sums and a cumulative sum is typical), I do the following:

Set up sheets 1 and 2 as required, and delete any other sheets in the workbook. Sheet 1 usually has nothing to add to itself, so sheet 2 is the first with a cumulative sum. I also rename the two sheets as required (e.g. &quot;Week 1&quot;, &quot;Week 2&quot

Then I select sheet 2 (&quot;Week 2&quot and use Move or Copy sheet on the Edit menu. Select &quot;(move to end)&quot; in the &quot;Before sheet&quot; box and check the &quot;Create a copy&quot; check box. This will put a copy of sheet 2 (renamed as &quot;Week 2 (2)&quot; in this example) after sheet 2.

Then amend the name of the new sheet (e.g. to &quot;Week 3&quot and change the one formula in the new sheet that still refers to &quot;Week 1&quot; to refer instead to &quot;Week 2&quot;. (If you had lots of fomulae, you could use Edit, Replace)

Then repeat the whole exercise, but instead of copying just &quot;Week 3&quot;, copy &quot;Week 2&quot; AND &quot;Week 3&quot;. Although two sheet names need amending, only one formula does.

Carrying on repeating the exercise copying all sheets (except sheet 1) each time. So next you are copying four sheets, then 8, then 16, etc. You soon get up to 52 or more sheets. Once you have set up a complete year, you can copy the whole year again and even put it into a different workbook.

Hope this is useful to someone.

DuckBill