Automatically updating a cell reference when new data is added

DMfba

New Member
Joined
Mar 21, 2019
Messages
36
Hi everyone,

I seem to be having an issue updating a cell reference when new data is added to table. I have built a little summary section in my workbook to display various information like Sales, Profit, etc… I have multiple summary charts to show different years. This summary sits by itself on its own Tab. Pictures shown below

1632251863436.png
1632251919469.png
1632251928997.png


Let’s say the labels in the 1st chart is in column A and data is in Column B. The data for FY2022 Sales in cell B2 would point to a data tab cell G2. My data tab is broken down by year and month.

1632251993654.png



Here is where the problem comes in. When new data(Sep) is added it is added to the top row and all data is pushed down by 1 row. The cell reference in each of the charts of the above do not change. However, only the FY2022 chart is correct because it points to the first row of data in the data tab. This workbook combines all the raw data in a table from Power Query.

I have another version of this workbook that stores the data on the data tab but not in a table format. However, when I manually add a new row, excel will automatically adjust the cell references. For example, FY 2020 Sales points to cell G20 on the data tab. Excel will automatically adjust that reference to G21 after a new row of data is added.

Using a table that automatically updates is much faster and cuts down on the file size. Is there a way to get excel to change the cell reference stored in the summary tab by 1-every time new data is added to the table on the data tab? I am not sure if the problem is that it’s referencing a table or maybe the table is generated from power query.

Can someone shed some light on this?

Thank You,
DM
 

Excel Facts

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

Scott R

Active Member
Joined
Feb 20, 2002
Messages
443
Office Version
  1. 365
Platform
  1. Windows
Rather than reference cells, you could reference the column in the table and use date criteria to pull the desired time period.
=SUMIF(Table1[DateCol], Date, Table1[Current Year Sales])
 
Solution

DMfba

New Member
Joined
Mar 21, 2019
Messages
36
Hi Scott,

Thank you for the help. I didn't think of using the table. the SumIFS function worked. I needed it to look for the year and the month. I should be able to use similar formulas for the different years and not have to worry when new months are added to the data table.

Thanks you
DM
 

DMfba

New Member
Joined
Mar 21, 2019
Messages
36
Hi Scott,

Switching to the table format does cause a problem with the average formula. I would need to take the average of a range of values depending on the date. For example, our fiscal year starts in July, so for this month I would need the values or range for FY 2022 Jul & FY 2022 Aug, then the following month I would need the range to include FY 2020 Sep.

Is there function or formula that would calculate the two points in the table range for average formula. For Example, at the end of the fiscal year in June, is there a way to use AverageIFS to calculate the average of all of the months between July and June? Is there a way for AverageIFS to calculate the average from July to a given month?

Thank You,
DM
 

Scott R

Active Member
Joined
Feb 20, 2002
Messages
443
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Probably yes, but hard to answer specifically without seeing your data. I typically enter the current month somewhere on the sheet and then have date range formulas elsewhere that define Current Year, Last Year, etc.
=AVERAGEIFS(Table1[numbersColumn], Table1[dateColumn], ">=" & beginDateForCurrYrCell, Table1[dateColumn], "<=" & endDateForCurrYrCell)
 

DMfba

New Member
Joined
Mar 21, 2019
Messages
36
Hi Scott,

thank you for the reply. I will try not to make this to confusing. Here is a sample of the data. One problem is the Month labels are text and not actual dates.

1632340611807.png



So in this example I am trying to take the Calendar Year average of the same time last year. This is the highlighted red box.

I tried using:

=AVERAGEIFS(Vend_Performance[Current Year Inventory],Vend_Performance[CY], Sheet1!$B$7,Vend_Performance[Fiscal Month],">="&Sheet1!$B$5,Vend_Performance[Fiscal Month],"<="&Sheet1!$B$2)

which point to ">="& Jan, and "<="& Aug - this returned a #div/0 error. Some how I would need to get the range between CY2020 Jan through CY 2020 Aug, but also have the ability to increase that range to Sep. Any thoughts on working around this?

I believe the other average functions will work as I won't need the AveragIFS, I can use the regular AverageIF and filter by year. It is only the partial year that is returning a error.
 

Scott R

Active Member
Joined
Feb 20, 2002
Messages
443
Office Version
  1. 365
Platform
  1. Windows
It would help to either bring in a date instead of text, or in your table, add a date column. Then you could get around the month as text problem.
=DATEVALUE([@[Fiscal Month]]&" 1, "&[@CY])
 

DMfba

New Member
Joined
Mar 21, 2019
Messages
36
Hi Scott,

Adding a date column worked perfect. Thank you for giving the formula as well.

Thank You,
DM
 

Forum statistics

Threads
1,148,422
Messages
5,746,587
Members
424,032
Latest member
pochie2741

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