Changing Variable Within Sum Of Specified Rows From Separate Sheet

bmoman

New Member
Joined
Feb 3, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,776
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top