# Changing Variable Within Sum Of Specified Rows From Separate Sheet

#### bmoman

##### New Member
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

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Any suggestions?

#### Eric W

##### MrExcel MVP
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.

Replies
1
Views
87
Replies
1
Views
238
Replies
0
Views
26
Replies
13
Views
314
Replies
1
Views
126

1,128,017
Messages
5,628,159
Members
416,296
Latest member
smartua

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back