Using indirect function

chemhany

New Member
Joined
Mar 3, 2021
Messages
28
Office Version
  1. 2019
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 ?? :unsure::unsure:
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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.
 
Upvote 0
Thanks for your reply and help
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
 
Upvote 0
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".
 
Upvote 0
Solution
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!
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top