INDIRECT to range syntax

[Arie Bos]

I have monthly sheets, in which many formulas refer to one other sheet called 'Trades'. The data in sheet 'Trades' is over 40 columns and for this month between row 402 and 493. So, I refer to e.g. =AVERAGE(Trades'!E403:E493))

But, there are so many formula's like this, then for next month, where data are is from row 494 to row 587, I have to edit all these formulas to refer to the new data range.
So, I thought to put the starting row number and ending row number (so 402 and 493 for this month, and 494 and 587 for next month) in a fixed cell location on each monthly sheet. I though to use INDIRECT to insert the numbers in all these formulas, so I would just have update these two numbers once per month to have all formula's looking in the right data range.

But how to do this in a range, like (Trades'!E403:E493) ?

 

[JoeMo]

Say in A1 you enter: E403:E493 on the sheet where you want the average value to be returned. Then in the cell where you want that value returned enter:
=AVERAGE(INDIRECT("'Trades'!"&A1))

 

[Jonmo1]

Is there anything added in another column to indicate which month each cell is related to?

So say D403:D493 = "May" or whatever
But then D494:DD587 will = "June" ?

Then you can use AverageIf

Something like
=AVERAGEIF(Trades!D:D,"May",Trades!E:E)

Then you could even make it use whatever the current month is, or you write the month in a cell
=AVERAGEIF(Trades!D:D,A1,Trades!E:E)
And you put May in A1

Then when a new month begins, you only have to write the month in A1 for the formulas to auto update.

 

[Arie Bos]

Thank you JoeMo and JonMo1

Both your solutions are great, and simple. Both solved my problem and allow me to make a much cleaner admin system in my excel.

Arie