Using indirect to prevent formula cells from moving down

[ehous31900]

I can not get "indirect" to work with the following formulas. It worked on a much older spread sheet that was not sorting by date or text and I assumed it would work here.

• I insert 7 to 15 rows a week by copying from a Garmin cvs export and "insert copied cells" with right click, on my new sheet.
• It is moving the 1st cell address down below the inserted.
• I thought a table and inserting one ro down leaving the top row empty would help. it did not.
• I show the non working formula in location with the " ' ".
• There is a few thousand rows already below and will have about 300 added per year.

Thank You for your help. I was so happy with the support i received a few weeks ago!

=SUMPRODUCT(INDIRECT(--(YEAR(B19:B10010)))=K2)*(INDIRECT(D19:D10010))

=SUMPRODUCT(--(YEAR(B19:B10010)=K2),--(LEFT(A19:A10010,5)="ROUVY"),D19:D10010)

#VALUE!

=AVERAGEIF(INDIRECT(--(YEAR($B$19:B10010))),2012,$M$19:$M10010)

=AVERAGEIF(INDIRECT(--(YEAR($B$19:B10010))),2012,$C$19:$C10010)

=MAXif(INDIRECT(--(YEAR($B$19:B10010))),2012,$N$19:$N10010)

 

[coulombevin]

Hi ehous31900,

It seems that you are using a table and adding rows randomly doesn't seems the best way to use it. First, I would add the data at the end, then use the filter button in the header of the table to sort by date.

Here's a solution using your table, you must change the name Table1 for your table name (top left in create table tab of the ribbon): *I am using the French version of Excel 365, so there might be some errors due to manual translation or discrepancies in the Excel user manual*

- Cell K3

=SUM(FILTER(Table1[Distance],YEAR(Table1[Date])=K2,0))

- Cell K4

=SUM(FILTER(Table1[Distance],(YEAR(Table1[Date])=K2)*(LEFT(Table1[Name],5)="ROUVY"),0))

- Cell K6

=AVERAGE(FILTER(Table1[Avg Speed],YEAR(Table1[Date])=K2,0))

- Cell K7

=AVERAGE(FILTER(Table1[Duration],YEAR(Table1[Date])=K2,0))

- Cell K8 *your screenshot show max ride miles as label while your formula is looking for max speed (N:N), so i used Distance for max distance instead of speed*

=MAX(FILTER(Table1[Distance],YEAR(Table1[Date])=K2,0))

Bests regards,

Vincent

 

[ehous31900]

Thank you Vincent. I will try this, Ed

 

[Peter_SSs]

I would definitely be avoiding the volatile function INDIRECT if possible.

Since you are using a formal Excel table I would be using the formal table nomenclature similar to @coulombevin but below I have ..

• Modified the formula structure so you can just copy/drag the formulas across without having to edit each one individually. As was mentioned above, check/edit the table name.
• Replaced the zero values with "" in some so that the formulas don't incorrectly give a zero value if there is no data for that year (it may not matter with your data if there is always some data for each year listed in row 2)

I have just used Distance for my few example formulas but you should be able to apply this idea to any measures you want.

For the future I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

ehous31900.xlsm
	A	B	C	D	E				I	J	K	L	M
1
2										Year	2012	2013	2014
3										Total Distance	266	411	579
4										Average Distance	66.5	58.71429	52.63636
5										Max Distance	92	98	93
6
7
16
17
18	Name	Date	Duration	Distance
19		16/12/2014		80
20		13/12/2014		91
21		12/12/2014		24
22		11/10/2014		93
23		7/09/2014		49
24		1/09/2014		42
25		2/08/2014		59
26		28/06/2014		30
27		19/02/2014		1
28		15/02/2014		81
29		19/01/2014		29
30		29/10/2013		64
31		24/07/2013		68
32		17/06/2013		72
33		20/05/2013		47
34		18/05/2013		98
35		22/03/2013		9
36		9/02/2013		53
37		31/08/2012		84
38		9/07/2012		92
39		11/06/2012		22
40		16/01/2012		68
41
42
Sheet1

Cell Formulas
Range	Formula
K3:M3	K3	=SUM(FILTER(Table1[[Distance]:[Distance]],YEAR(Table1[[Date]:[Date]])=K2,0))
K4:M4	K4	=AVERAGE(FILTER(Table1[[Distance]:[Distance]],YEAR(Table1[[Date]:[Date]])=K2,""))
K5:M5	K5	=MAX(FILTER(Table1[[Distance]:[Distance]],YEAR(Table1[[Date]:[Date]])=K2,""))

 

[ehous31900]

Both of the above worked, the mark as solution would only allow me to select one.

 

[Peter_SSs]

Thanks for the confirmation. Glad we could help.