Sheet Reference Formula Help

I have a formula below:

=IFERROR(AVERAGEIF('02-09'!$E$2:$E$50,B4,'02-09'!$J$2:J50),"") that I would like to change

In cell D3 of the current sheet I have a function that return the sheet name for a dependent sheet. For example:
=sheetname('02-09'!$B$16) would return 02-09.

Instead of referring to sheet 02-09 I would like the formula to be dynamic and reference D3 of the current sheet like

=IFERROR(AVERAGEIF(D3&!$E$2:$E$50,B4, D3&$J$2:J50),"").

What is the proper formula that I can use? Would INDIRECT work?

I attached a copy of the spreadsheet

hi K m. yes INDIRECT would work.
=IFERROR(AVERAGEIF(INDIRECT("'"&$D$3&"'!$E$2:$E$50"),B4,INDIRECT("'"&$D$3&"'!$J$2:$J$50")),"")

Originally Posted by benishiryo

hi K m. yes INDIRECT would work.
=IFERROR(AVERAGEIF(INDIRECT("'"&$D$3&"'!$E$2:$E$50"),B4,INDIRECT("'"&$D$3&"'!$J$2:$J$50")),"")

We'll need to add to this.
The original formula had reference to $J$2:J50 <--notice lack of $'s on J50
Indicating the formula is filled and expecting the J50 to incriment.

the J50 will not incriment in the Indirect formula. (also no need for the $'s in the indirect text string)

Try
=IFERROR(AVERAGEIF(INDIRECT("'"&$D$3&"'!E2:E50"),B4,INDIRECT("'"&$D$3&"'!J2:"&CELL("ADDRESS",J50))),"")

Sorry, I couldn't get it to work.

Here is my new formula

IFERROR(AVERAGEIF('03-30'!$E$2:$E$150,$B4,'03-30'!$J$2:N50),"")

I want to replace 03-30! with what is in cell H3 of this worksheet

I have attached a sample file and highlighted in orange the cell in question

@Jonmo1:
i was just lazy to remove the $ signs. and you did that too in post#5 for column E. as for the lack of dollar sign in J50, it is supposed to have a dollar sign if we're not using INDIRECT. so it doesn't have an impact. you cannot use a single column for range & multiple columns for the average_range. so i guess K m needs this in D4:
=IFERROR(AVERAGEIF(INDIRECT("'"&D$3&"'!E2:E150"),$B4,INDIRECT("'"&D$3&"'!J2:J150")),"")

this will give the same result as putting in up till column N, which is unnecessary because it's not calculating it.