auto-fill for worksheets?

**marymo** · 01-26-2011, 11:06 AM

I would like to carry a pattern across worksheets similar to using the auto fill handle between cells. My project includes the following formula on the second worksheet

=IF(F19=0,'JAN 31'!G3,SUM('JAN 31'!G3+IF(F19=0,"",(SUM(4-(SUM(G7:G19)))))))

and the following formula on the third worksheet

=IF(F19=0,'FEB 14'!D3,SUM('FEB 14'!D3+IF(F19=0,"",(SUM(4-(SUM(H7:H19)))))))

etc. Is there a way to "auto fill" the remaining worksheets to make them refer to the worksheet prior?

**Cheeky Charlie** · 01-26-2011, 01:39 PM

You don't need to put SUM() around every operation, if this:
='Jan 31'!G3+IF(F19=0,0,4-SUM(G7:G19))
Does what you originally intended then I would hope this is now simple enough for you to edit manually, rather than create a more complex solution to do what you originally requested.

Incidentally:
IF(true/false,True scenario,IF(same true/false,<doesn't matter, the same question can't evaluate to both true and false at the same time!>,False scenario)

hth

**marymo** · 01-26-2011, 02:16 PM

Point taken and thanks so much for the help. The simpler version you provided worked fine (and was a learning opportunity). But to my original question- is no "auto-fill" type command for working across worksheets? I have 26 worksheets with multiple formulas each that will need to be manually changed to refer to the worksheet immediately preceeding it. Just hoping for a quicker solution.

Thank you for your time!

**mahju** · 01-26-2011, 03:30 PM

Hi

@marymo

In the question the second worksheet has
Jan 31and the third has Feb 14
It is 15 day working
If so I think it should be done with a macro / VBA

Thanks

Regards

**Cheeky Charlie** · 01-27-2011, 05:51 AM

No.

You can put the same formula in the same cell on multiple sheets by selecting multiple sheets (hold ctrl or shift, then click the relevant sheet tabs) - but this wouldn't include reference offsets per sheet as you've outlined. If each sheet included the name of the sheet on it (e.g. if the Jan 31 sheet had Jan 31 in the top left corner) then you could use this to calculate the name of the preceding sheet and insert it into an indirect() formula - which would work, assuming you had constant differences between sheet dates (there appears to be some inconsistency, alluded to above). In my opinion this would not be worth the overhead compared to writing a simple formula and changing it manually if you needed to.

hth