excel copying formula with few different sheets

1
votes

I have different sheets. named January. 5, 6, 7, 8 and so on.

on January 5 SHEET1:

       A1            B1      C1          D1
beginning balance + debit - credit = ending balance

on January 6 SHEET2: I want the beginning balance amount of the ENDING BALANCE OF JANUARY 5.

on January 7 SHEET3: I want the beginning balance amount of the ENDING BALANCE OF JANUARY 6.

and so on.

What shall I do to make my cell copy the amount of the ending balance of the previous sheet together copying the formula when I copy the previous sheet?

1
excelcopyformula

1 Answers

1
votes

Put this formula into A1.

=INDIRECT("'"&TEXT(DATEVALUE(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1)))-1,"MMMM d")&"'!D1")

Obviously, you have to type in A1 of your 1st sheet/date since we have nothing to reference to.

"filename" does not work for a new workbook that has never been saved.

CELL("filename",A1)

Despite the name, it returns the workbook and worksheet name. The functions around that is to isolate to the worksheet name.

DATEVALUE

Tries to convert your worksheet name, which is a date, into Excel's internal date representation.

TEXT

Converts date back to your date format consitent with your sheet name after determining the prior day -1.

INDIRECT

Evaluates the text we just formed as an Excel formula.

Just ask if you want more details. You can try the "Evaluate Formula" button go go through the formula step by step.