Automatically updating a cell reference when new data is added

[DMfba]

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

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.

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

 

[Scott R]

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])

 

[DMfba]

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]

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]

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]

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.

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]

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]

Hi Scott,

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

Thank You,
DM