coping cells to new worksheet ells

Copper Contributor

Hi all.  I am working with Excel 2016.

I have a workbook with 12 sheets in it.  I use it my bill paying book. Each sheet is a month of the year.  What I am trying to do is the following. Some of the cells have a formula where it calculates a balance on some loans.  

For example, Cell C3 has a constant value. Cell D3 shows the amount that was paid for that month.  Cell B3 has the formula =B3-D3. I want to take the value in B3 (after calculations) to sheet 2 and do the formula over again. Then have sheet 3 take the value of sheet 2 B3 and do the formula, sheet 4 takes the value of sheet 3 B3 and do the formula again. I need this to happen for all 12 worksheets. I have several rows that I would like to do this with.

I really am not very good with Excel and even worst trying to use VB.

I  am hoping to put this in the first sheet, copy all of the 1st sheet and just paste into the other 11 sheets and have the formula properly populate to each new sheet.

If this is at all possible, any help would be great full.

4 Replies

@texasowl I know it sounds like a great idea to have one worksheet per month, but it actually makes summary analysis difficult. If you put all your transactions in one sheet, then you can easily use PivotTables to summarize it all.

 

If you do want to go the formula route, then one way is to put the previous month in an anchor cell in each sheet, and then use INDIRECT to return the value from that sheet. This will return the value from January!B3, where the word "January" is in cell B1:

 

=INDIRECT($B$1&"!B3")

 

Now you can copy that to your other sheets, and don't need to adjust anything.

 

 

@texasowl 

Initial formula

 Cell B3 has the formula =B3-D3

won't work since has circular reference. Perhaps you mean something else.

@Smitty Smith,  Thank you for the assist, however, I think this is just getting a little too much for me? I am not really familiar enough with Excel. I can just keep it as it is.

Thank you again for your help.

Thank you for your advice.
Have a great day.