Copy a formula in Excel from one worksheet to another and have worksheet name in formula update

Question

I have an Excel workbook with each months accounting sheet tabs, Jan > Dec. In opening debits, I have a formula:

=IF(JAN!D31>0,JAN!D31,JAN!-D31)  

which says, if D31 is greater than zero, then copy that value to G4 (opening debits); if D31 is 0 or a minus, then change the value for G4 to show a plus so it adds to my Debits column for a new monthly total.

I can copy the formula to each month @ G4, but since I have 12 months of worksheets, I am currently having to manually edit each months G4 to change the month, i.e. =IF(JAN!D37>0,JAN!D37,-JAN!D37) to, for instance, =IF(FEB!D37>0,FEB!D37,-FEB!D37). I want the month to change but not the cell reference.

Is there a way to copy the formula to Feb through December and have the month portion change appropriately? I can supply a screenshot.

I tried Microsoft Community for answers but they were not able to come up with anything. Otherwise, I have nothing else to try.

Answer 1

Home me that you want every month to use line 31 as a criterion ... In this case, try clicking the cursor about the reference cell "jan! D31" and change the text to "jan! D $ 31" inserting a $ between "D" and "31". After that copy and paste or drag the formula.

Answer 2

The key to solve this is the function INDIRECT. First you need a list of the month names / sheet names in some column. Lets say those month names are in column H, rows 4, 5, 6, ... . In G4, replace

  =IF(JAN!D37>0,JAN!D37,-JAN!D37)

by

  =IF(INDIRECT(H4) & "!D37">0,INDIRECT(H4) & "!D37",-INDIRECT(H4)  & "!D37")

Now, when you copy-fill down the formula of column G, H4 will be automatically replaced by H5, H6, ..., and each new formula refers to the correct month.

If you only want a single place to change the month name (keeping the formula in G4), this will work too, just edit the month name in H4.