AVERAGEIF + INDIRECT + DATE Function Help

[theinquirer]

Hello,

This is what I am trying to achieve with my formula:

1. Reference another cell in the list on the same page that would contain the sheet name and pull down the formula without having to manually type the sheet names.
2. If it's within the certain date range in the current month, then take the average of the data.


What I currently have:
=AVERAGEIFS(HELLO!$B:$B,HELLO!$A:$A,”>"&DATE(YEAR(TODAY()),MONTH(TODAY()),1),HELLO!$A:$A,""&DATE(YEAR(TODAY()),MONTH(TODAY()),31))


What I want to accomplish:
=AVERAGEIFS(INDIRECT"'"&A1&"'!"&A:A),2,0),INDIRECT"'"&A1&"'!"&B:B),2,0)”>"&DATE(YEAR(TODAY()),MONTH(TODAY()),1),INDIRECT"'"&A1&"'!"&A:A),2,0),""&DATE(YEAR(TODAY()),MONTH(TODAY()),31))


Breakdown:
1. A1 = Sheet Name
2. A:A = Contains the date that I want to lookup
3. B:B = Contains the data that I want to average after A:A is found

[protonLeah]

double post:
https://www.excelforum.com/excel-for...tion-help.html

[FDibbins]

Hard to say for sure without seeing some sample data, but based on your 2nd formula, you have some " either missing or out of place...
=AVERAGEIFS(INDIRECT"'"&A1&"'!"&A:A),2,0),INDIRECT"'"&A1&"'!"&B:B),2,0)”>"&DATE(YEAR(TODAY()),MONTH(TODAY()),1),INDIRECT"'"&A1&"'!"&A:A),2,0),""&DATE(YEAR(TODAY()),MONTH(TODAY()),31))
=AVERAGEIFS(INDIRECT("'"&A1&"'!A:A"),2,0),INDIRECT("'"&A1&"'!B:B"),2,0)”">"&DATE(YEAR(TODAY()),MONTH(TODAY()),1),INDIRECT("'"&A1&"'!A:A"),2,0),"<"&DATE(YEAR(TODAY()),MONTH(TODAY()),31))
What are you trying to do with the BOLDED parts?

and what are you trying to do here...
DATE(YEAR(TODAY()),MONTH(TODAY()),1)
DATE(YEAR(TODAY()),MONTH(TODAY()),31)

[FDibbins]

The other thread has been closed