Changing Variable Within Sum Of Specified Rows From Separate Sheet

[bmoman]

Good day Excel masters,

This regards a summary and expense excel file with worksheets within it for the purpose of monitoring rental property expenses and profits. The sheets are yearly summaries of all rental properties and a sheet per property as well.

Here's an example of how a sum of specified rows are pulled from worksheets as of now:
='2'!C$4+'2'!C$7+'2'!C$8+'2'!C$9+'2'!C$10+'2'!C$11

So in this example I'm getting a sum of rows 4,7,8,9,10, and 11(which are the expenses) in column C(which is the year) from sheet named 2 (2 is the 2nd rental property)

My goal is to minimize the amount of replacing text/copying/pasting I have to change in the summary sheets. The way it currently stands, each new year I would have to find and replace C to D and paste those back into their respective spots.

I've attempted a workaround to change C to D using =SUBSTITUTE(P13, "C", O13) where P13 represents '2'!C$4+'2'!C$7+'2'!C$8+'2'!C$9+'2'!C$10+'2'!C$11 , C represents the letter I want to change, and then O13 is a cell with the letter I want to change C to, ie. D.

However, when I put =Q13 in the summary expenses cell (Q13 contains the =SUBSTITUTE(P13, "C", O13) formula), it only displays 2'!D$4+'2'!D$7+'2'!D$8+'2'!D$9+'2'!D$10+'2'!D$11 in the summary expenses cell, whereas if I actually paste =2'!D$4+'2'!D$7+'2'!D$8+'2'!D$9+'2'!D$10+'2'!D$11 into the expenses cell, it will sum those values from rows 4,7,8,9,10, and 11 in column D in sheet 2 as I want.

I have attempted using the INDIRECT function but there is probably an issue with my syntax.

Thank you for your time and expertise

 

[bmoman]

Any suggestions?

 

[Eric W]

Welcome to the forum!

First, how do the values get moved from column C to column D? If you're inserting a new column before column C, then Excel will automatically update any formulas that refer to column C, and you don't need to worry about changing the formulas. If you cut and paste the values, that works the same way.

If the values are moved manually, or via macro, you could try some kind of MATCH function. For example, if you have the year in C1, and then next year you have another year in C1 and 2021 moves to D1, then you could have a formula that looks for the year in the heading, and adds up the appropriate cells in that column.

If that doesn't answer your question, could you show a sample of your sheet? If you use the XL2BB tool (see the link in my signature), it would make it easy to see what you have and how to handle it. It's easy to install and use.