Using indirect function

chemhany

New Member
I have workbook consist of 63 worksheets. They are arranged as following : in-out-in(1)-out(1)-in(2)-out(2) ........... in(31)-out(31)-Average
In average worksheet (the last one), I have 2 tables to calculate data average from each sheet. Table for data from ins worksheets and other one for data from outs worksheets.
How to use both Average and indirect functions to do that ?? What is the formula to be used when drag it down in each table enabled the increment of reference sheet name (in or out) ??
=AVERAGE(In!G13:G56) this is the function which I want to combine with indirect one and when drag it down, in changed to in(1) and so on
Is that possible ??

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Joe4

So for the "ins":
- the first formula should average the range on the "in" sheet
- the second should average the range on the "in(1)" sheet
- the third should average the range on the "in(2)" sheet
- etc

Is that correct?

We can probably make use of the INDIRECT formula in conjunction with ROWS() and COLUMNS(), if you let us know the exact cell addresses the first two formulas are being placed in.

chemhany

New Member
Yes, that is correct
For the average from ins sheets, it will be in B2
For outs sheets, it will be in J2
As I mentioned after in sheet, there is out sheet and after in(1) sheets, there is out(1) sheet and so on

Joe4

OK, since you have different formats (the first one is just "in" while the second is "in(1)"), I would entter the normal formula in cell B2, i.e.
Excel Formula:
``=AVERAGE('in(2)'!G13:G56)``
and then in cell B3, enter the formula:
Excel Formula:
``=AVERAGE(INDIRECT("'in("&ROW()-2&")'!G13:G56"))``
and copy down for the test of the sheets.

Do the same thing for the "out", just swapping the word "out" for "in".

chemhany

New Member

Thanks
It is working well

Joe4

You are welcome!

The key is this part of the formula:
Excel Formula:
``ROW()-2``

What that does is take whatever row the formula is located in, and subtract two off of it.
So, since we are first putting it in cell B3, it evaluates to 3-2=1.
As we copy the formula down the column, the row number returned by ROW() increases by 1 each time, increasing our row reference in the INDIRECT formula by 1 each time.

Hope that makes sense!

Replies
1
Views
94
Replies
1
Views
113
Replies
1
Views
212
Replies
3
Views
72
Replies
0
Views
81

1,147,482
Messages
5,741,407
Members
423,657
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back