question on INDIRECT function 1

[westma]

I am using the INDIRECT function to reference a range for information. My formula looks like:

=AVERAGE($B$57:INDIRECT($E$44)), where $E$44 contains something like $B$100.

My problem is I need to use a formula like this on a different sheet referenced back to my original, something like:

=AVERAGE(Sheet2!$B$57:INDIRECT(Sheet2!$E$44)),

but for some reason this does not work to give me the same value as on the first worksheet. Does anyone have any suggestions on what I need to do differently to make this work?

 

[Zathras]

I'm not exactly clear on what you have where. Usually when referring to "a different sheet" that would be a reference to "Sheet2" and referring to "my original" would be a reference to "Sheet1" but it looks like your situation is exactly reversed.

So... If your data is on "Sheet2" and your cell $E$44 is on "Sheet2" and all you want on "Sheet1" is a formula, then I guess this is what you are looking for:

  =AVERAGE(Sheet2!$B$57:INDIRECT("Sheet2!"&Sheet2!$E$44))

But it would be easier to use the simpler original form of INDIRECT on sheet2 and just reference that result from sheet1 with a simple formula like

  =Sheet2!A39

Why make life more difficult than it already is?

 

[westma]

Zathras,

Thank you for such a marvelous interpretation of a very poorly written question. You got it just right and I was able to fix my problem. Amazing! I guess I just have a case of the Monday's.

Rgds,
matt

 

[MarkBeck]

A Question To Zathras

What triggers the #VALUE! error massage and why does prefixing "Sheet1"& correct the situation?

Although this is not in request of a practicle situation (as the formula works) i would nevertheless benefit from understanding the dynamics behind it.

Thanks


Mark

 

[Zathras]

Mark. I don't really understand the dynamics of it ether. If you play around with the possible variations, you should eventually come to an understanding of what works and what doesn't. I don't have any inside knowledge of what goes on at Micro$oft, but I do enjoy experimenting.

I'm not sure if this will answer your question, but consider the possible ways to put a formula on "Sheet1" that references the data from "Sheet2" -- Both of the following work and seem to be equivalent:

   =AVERAGE(Sheet2!$B$57:$B$100)
   =AVERAGE(Sheet2!$B$57:Sheet2!$B$100)

Now, if we want to supply the $B$100 via INDIRECT from Sheet2, there appear to be two possibilities (replacing

 $B$100

with

 INDIRECT(Sheet2!$E$44)

---

   =AVERAGE(Sheet2!$B$57:INDIRECT(Sheet2!$E$44))
   =AVERAGE(Sheet2!$B$57:Sheet2!INDIRECT(Sheet2!$E$44))

but, surprise! neither one works.

Sheet2!$E$44

is

 $B$100

but the AVERAGE function then tries to use $B$100 from Sheet1 which of course doesn't work in conjunction with $B$57 from Sheet2. I would have expected the second form to work, but apparently the Redmond programmers didn't allow for that particular syntax with INDIRECT.

Next I tried

   =AVERAGE(Sheet2!$B$57:"Sheet2!"&INDIRECT(Sheet2!$E$44))

but that doesn't work either. Finally, I tried

   =AVERAGE(Sheet2!$B$57:INDIRECT("Sheet2!"&Sheet2!$E$44))

and somewhat to my amazement, that worked.

Here is another way it could be done. Since the AVERAGE function wants a particular shape to it's argument, this formula will generate the string that looks like the range that is wanted:

    ="Sheet2!$B$57:"&Sheet2!$E$44

and if that formula is placed in cell B19 of Sheet1, for example, then this function in Sheet1 will also produce the desired average:

   =AVERAGE(INDIRECT(B19))

 

[xlbo]

Think you'll find that the reason that:
=AVERAGE(Sheet2!$B$57:"Sheet2!"&INDIRECT(Sheet2!$E$44))
doesn't work and:
=AVERAGE(Sheet2!$B$57:INDIRECT("Sheet2!"&Sheet2!$E$44))
does is that INDIRECT takes a STRING argument - the only reason that =INDIRECT(A1) works is due to excel using the default property of the range object - ergo, "Sheet2!"&Sheet2!$E$44 equates to:
"Sheet2!" and the text which is in Sheet2!E44
which is a string
which works

:"Sheet2!"&INDIRECT(Sheet2!$E$44)
doesn't work as "Sheet2!" is outside indirect and therefore cannot be resolved - pretty much the same issue as the other alternatives you posted


Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[MarkBeck]

Thank you Zathras & Xlbo for your time and elaboration.

Mark