copy formulas from worksheet to another

Hi Ken. I am an Excel user like you.

I have an Excel workbook with each months accounting sheet tabs, Jan > Dec. In opening debits, I have a formula; =iF(D31>0,D31,-D31)

You can copy this formula from one sheet to another without it automatically adjusting and adding a sheet name.

1. Select the cell with the original formula with no sheet name included.
2. Highlight the formula in the Formula Bar and copy (CTRL+C).
3. Click the X to the left of the Formula Bar to exit the cell without making changes.

4. You can now paste that formula into any sheet, and it will not add a sheet name to the formula and will refer directly to the cells in the sheet you pasted it into.

I don't believe this is the answer. Your example shown, does not include the previous month which I need.

I WANT the copied formula to reference the previous sheet (month), But, I want the month to change just as if you were had a cell with a formula and filled down or horiz.

For instance, the formula in February is =IF(JAN!$D$31>0,JAN!$D$31,-JAN!$D$31). See that this references January. When I copy to March, April, etc. I want the month to change but not the Cell reference.

So, February is =IF(JAN!$D$31>0,JAN!$D$31,-JAN!$D$31), March should be =IF(FEB!$D$31>0,FEB!$D$31,-FEB$D$31) and so on for other months.

As it stands right now, I have to copy the formula to each sheet, then spend an hour editing the months. If you will notice on my attached sample, there are 2 debit entries at row 4; one for KS debits and one for Andy debits. This means that I have to spend over an hour editing these cells, one by one.

Your example shown, does not include the previous month which I need.

OK. But, if I have 12 seperate worksheets, labeled Jan, Feb, all the way to December, will it change the formula in each one t o reflect the previous month?

No, you would need to do the Replace in each worksheet to match reflect the old and new sheet names. But since it does all on the sheet at the same time it is only one operation per sheet rather than editing multiple entries on every sheet.

To create the new sheet name in the formula would require some type of formula referencing the desired sheet name somewhere in the sheet, but I know of no way to reference that inside of the formula to create a new sheet name.

Something to play with but hopes aren't high.

OK. But, if I have 12 seperate worksheets, labeled Jan, Feb, all the way to December, will it change the formula in each one t o reflect the previous month?

OK, I think I understand the issue now. I was thinking that Excel was inserting the name of the sheet that the formula was copied from when it was copied to a new sheet.

I would go ahead and copy the formulas into the new month and then use Find and Replace to change all of the sheet references at once. So, in your example when moving from February with JAN! sheet references to a new March sheet, set up the new March sheet with the formulas still referring to January as you are doing but instead of editing them manually use Find and Replace.

Highlight the ranges or the columns with the formulas, open the Replace dialogue (CTRL+H), enter JAN! in the Find What line and FEB! in the Replace With line. If you have selected more than one cell, click Replace All and Excel will replace every instance of JAN! with FEB! (if you selected only one cell you would need to just click Replace).

VERY, VERY IMPORTANT NOTE: When using Replace Excel will replace every instance of the string even if it is in the middle of another word where you don't want it replaced so it is important to use a string to find that is unique which is why I included the "!" because that should only occur following the sheet name in the formula. Just be sure to check the entire range or column if that is what you select for any possible places where Excel could replace something you don't want replaced.